0

I have two functions with a similar signature, lets say:

CREATE FUNCTION func1(INOUT new "My_Table") AS $$ ... $$ LANGUAGE plpgsql;
CREATE FUNCTION func2(INOUT new "My_Table") AS $$ ... $$ LANGUAGE plpgsql;

I call both of these functions from a BEFORE UPDATE TRIGGER where the parameter is the row being updated. I want to give the row to the function as an input but subsequently modify it inside the function (therefore the INOUT).

I call the first function as:

SELECT * FROM func1(NEW) INTO NEW;

Which works just fine.

However, I can't make it work with the second function because the type of NEW is no longer of "My_Table" but it is "record" and I am unable to cast it.

SELECT * FROM func1(NEW) INTO NEW;

or

SELECT * FROM func1(NEW::"My_Table") INTO NEW;

or

SELECT * FROM func1(CAST(NEW AS "My_Table")) INTO NEW;

gives

ERROR:  42846: cannot cast type record to "My_Table"

How can I do this please?

  • 2
    SELECT ... INTO somename; creates new table somename: doc. Is it really what you want? If you want to assign a query result to the plpgsql variables you should to use SELECT * INTO NEW FROM func1(NEW); or probably just NEW := func1(NEW);. – Abelisto Feb 18 '17 at 8:31
  • NEW := func1(NEW); works! SELECT * INTO NEW FROM func1(NEW); gives ERROR: 42846: cannot cast type record – Jana Feb 18 '17 at 17:00
  • also a solution I found is to cast to the text first – Jana Feb 18 '17 at 17:01
2

These are the two working solutions:

NEW := func1(NEW);

or first cast the record to text:

SELECT * FROM func1(NEW::text::"My_Table") INTO NEW;

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.