On Wed, Mar 28, 2012 at 1:52 PM, Kenneth Tilton <ktil...@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;
Ideas welcome.
--kt
>
> -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');
>>
>>
>
