Обсуждение: How return a row from a function so it is recognized as such by caller?

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...

-kt

On Wed, Mar 28, 2012 at 12:54 PM, Kenneth Tilton <ktilton@mcna.net> wrote:

On version:

    PostgreSQL 9.1.2 on x86_64-unknown-linux-gnu, compiled by gcc (GCC) 4.4.5 20110214 (Red Hat 4.4.5-6), 64-bit

I get this error (all code at end of post) in pgAdmin:

NOTICE:  bpa inbound (,now_plus_30)

CONTEXT:  SQL statement "select now_plus_30(NEW)"

PL/pgSQL function "bp_alert_init" line 7 at SQL statement

NOTICE:  warn time in input row = ("2012-04-27 16:41:20.338239+00",now_plus_30)

CONTEXT:  SQL statement "select now_plus_30(NEW)"

PL/pgSQL function "bp_alert_init" line 7 at SQL statement

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

CONTEXT:  PL/pgSQL function "bp_alert_init" line 7 at SQL statement

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

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

SQL state: 22007

Context: PL/pgSQL function "bp_alert_init" line 7 at SQL statement

Note that I have eliminated the complexity of the callback and simply call the desired initializer directly. FWIW, using the desired EXECUTE statement produces exactly the same error. 

If I declare the receiving variable to be a record, pgAdmin shows me this:

NOTICE:  bpa inbound (,now_plus_30)

CONTEXT:  SQL statement "select now_plus_30(NEW)"

PL/pgSQL function "bp_alert_init" line 7 at SQL statement

NOTICE:  warn time in input row = ("2012-04-27 16:46:22.62478+00",now_plus_30)

CONTEXT:  SQL statement "select now_plus_30(NEW)"

PL/pgSQL function "bp_alert_init" line 7 at SQL statement

NOTICE:  caller got bpa ("(""2012-04-27 16:46:22.62478+00"",now_plus_30)") <----------------- 

ERROR:  record "bpa" has no field "warn_time"

CONTEXT:  SQL statement "SELECT bpa.warn_time"

PL/pgSQL function "bp_alert_init" line 9 at RAISE

So it looks as if I have to "unwrap" or eval the return value (or change the way I am returning it). But the callee is declared as returning a bp_alert and returns a variable of type bp_alert, so I am not sure what more I can do in the callee. The caller is selecting into a variable of type bp_alert, so that too seems clear.

Hints welcome, code next.

ken

-- code starts here -------------------------------------------------------

set search_path to public;

drop table if exists bp_alert cascade;

CREATE TABLE bp_alert (

  warn_time          timestamp WITH TIME ZONE,

  warn_time_init     text

)

CREATE OR REPLACE FUNCTION now_plus_30(bpa bp_alert)

  RETURNS bp_alert AS

$BODY$

  

declare

begin

   raise notice 'bpa inbound %', bpa;

   bpa.warn_time = now() + interval '30 days';

   raise notice 'warn time in input row = %', bpa;

   return bpa;

end;

$BODY$

  LANGUAGE plpgsql VOLATILE;

CREATE OR REPLACE FUNCTION bp_alert_init()

  RETURNS trigger AS

$BODY$

  

declare

   bpa bp_alert; -- make this a record and the "warn time in caller" raise fails on bpa not having warn_time

begin

   -- no difference: execute 'select ' || NEW.warn_time_init || '($1)' using NEW into bpa;

   select now_plus_30(NEW) into bpa;

   raise notice 'caller got bpa %', bpa;

   raise notice 'warn time in caller now %', bpa.warn_time;

   return bpa;

end;

$BODY$

  LANGUAGE plpgsql VOLATILE;

drop trigger if exists bp_alert on bp_alert;

CREATE TRIGGER bp_alert

  BEFORE INSERT

  ON bp_alert

  FOR EACH ROW

  EXECUTE PROCEDURE bp_alert_init();

insert into bp_alert (warn_time_init) values ('now_plus_30');

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

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

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

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

On Wed, Mar 28, 2012 at 2:08 PM, Kenneth Tilton <ktilton@mcna.net> wrote:
> 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:
>

your'e missing a 'select' in there.  functions must be called through
select (just like you can't call a function by name in psql without
it).

On Wed, Mar 28, 2012 at 2:49 PM, Kenneth Tilton <ktilton@mcna.net> wrote:
> Well then I have the other error. With this code:
>
>    execute 'select ' || NEW.warn_time_init || '($1)' into bpa using NEW;

try this:
select * from now_plus_30(NEW) into bpa;