Re: How return a row from a function so it is recognized as such by caller?

[Kenneth Tilton <ktilton@xxxxxxxx>]

> >

On Wed, Mar 28, 2012 at 2:36 PM, Merlin Moncure <mmoncure@xxxxxxxxx> wrote:

On Wed, Mar 28, 2012 at 1:11 PM, Kenneth Tilton <ktilton@xxxxxxxx> wrote:
>
>
> On Wed, Mar 28, 2012 at 1:52 PM, Kenneth Tilton <ktilton@xxxxxxxx> wrote:
>>
>> First, apologies for being too succinct. I should have reiterated the
>> message subject to provide the context: I am just trying to return a row
>> from a function and have the caller understand it. Oh, and I am a nooby so
>> it is probably something daft.
>>
>> Second, I just tried returning the row as an out variable and got the same
>> result. I'll try messing with the caller...
>
>
> OK, this works in re getting the row back:
>
>     bpa := now_plus_30(NEW);
>
> But I need to execute an arbitrary function passed in as text, and I now
> realize EXECUTE is for SQL and I am trying to use it to "eval" plpgsql and
> those are different animals.
>
> I see no plpgsql equivalent of EXECUTE, ie where I can build up a plpgsql
> statement like this:
>
>    execute 'bpa := ' || function_name || '($1)' using NEW into bpa;

If all you are doing is assignment into a variable, you can use
EXECUTE...INTO...USING. That should work.

Thanks, Merlin. Maybe I have some subtle detail wrong. When NEW.warn_time_init is 'now_plus_30' and I have this as my execute statement:

        execute NEW.warn_time_init || '($1)' into bpa using NEW;

   

...I get:

ERROR:  syntax error at or near "now_plus_30"

LINE 1: now_plus_30($1)

        ^

QUERY:  now_plus_30($1)

CONTEXT:  PL/pgSQL function "bp_alert_init" line 6 at EXECUTE statement

********** Error **********

ERROR: syntax error at or near "now_plus_30"

SQL state: 42601

Context: PL/pgSQL function "bp_alert_init" line 6 at EXECUTE statement

Using a more conventional syntax I am back to where I started:

execute 'select ' || NEW.warn_time_init || '($1)' into bpa using NEW;

....produces: 

NOTICE:  bpa inbound (,now_plus_30)

CONTEXT:  SQL statement "select now_plus_30($1)"

PL/pgSQL function "bp_alert_init" line 6 at EXECUTE statement

NOTICE:  warn time in input row = ("2012-04-27 19:04:37.793835+00",now_plus_30)

CONTEXT:  SQL statement "select now_plus_30($1)"

PL/pgSQL function "bp_alert_init" line 6 at EXECUTE statement

ERROR:  invalid input syntax for type timestamp with time zone: "("2012-04-27 19:04:37.793835+00",now_plus_30)"

CONTEXT:  PL/pgSQL function "bp_alert_init" line 6 at EXECUTE statement

********** Error **********

ERROR: invalid input syntax for type timestamp with time zone: "("2012-04-27 19:04:37.793835+00",now_plus_30)"

SQL state: 22007

Context: PL/pgSQL function "bp_alert_init" line 6 at EXECUTE statement

cheers, ken