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 <ktil...@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');
>
>
