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

>
>
>> On Wed, Mar 28, 2012 at 2:36 PM, Merlin Moncure <mmoncure(at)gmail(dot)com>wrote:

> On Wed, Mar 28, 2012 at 1:11 PM, Kenneth Tilton <ktilton(at)mcna(dot)net> wrote:
> >
> >
> > On Wed, Mar 28, 2012 at 1:52 PM, Kenneth Tilton <ktilton(at)mcna(dot)net>
> 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