From: | Kenneth Tilton <ktilton(at)mcna(dot)net> |
---|---|
To: | pgsql-general(at)postgresql(dot)org |
Subject: | How return a row from a function so it is recognized as such by caller? |
Date: | 2012-03-28 16:54:54 |
Message-ID: | CAECCA8a-39_75MPifTzEuc28rfMg0r8sOi6QqFhvhveq7QhJxQ@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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');
From | Date | Subject | |
---|---|---|---|
Next Message | leaf_yxj | 2012-03-28 16:55:38 | system catalog privilege and create privilege ??? how to control them?? thanks |
Previous Message | leaf_yxj | 2012-03-28 16:54:42 | Limit the normal user to see system catalog or not??? And create privilege??? |