pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedpolymorphic function in 7.4 vs. 8.3
6+ messages / 3 participants
[nested] [flat]
* polymorphic function in 7.4 vs. 8.3
@ 2009-06-11 20:16 Richard Rosenberg <richrosenberg@earthlink.net>
0 siblings, 1 reply; 6+ messages in thread
From: Richard Rosenberg @ 2009-06-11 20:16 UTC (permalink / raw)
To: pgsql-sql
I have the following setup which works great in version 8.3 but throws an
error in 7.4:
CREATE TABLE atest1
(
id integer NOT NULL,
descr text,
CONSTRAINT atest1_pkey PRIMARY KEY (id)
);
CREATE OR REPLACE FUNCTION test_trg()
RETURNS "trigger" AS
'
DECLARE
any_rec wfsys.atest1;
BEGIN
any_rec.id := NEW.id;
any_rec.descr := NEW.descr;
select into any_rec * from dd_test(any_rec);
--any_rec := dd_test(any_rec);
RETURN any_rec;
END;
'
LANGUAGE 'plpgsql' VOLATILE;
CREATE OR REPLACE FUNCTION dd_test(anyelement)
RETURNS record AS
'
DECLARE
any_rec alias for $1;
some_row record;
BEGIN
some_row := any_rec;
if some_row.id < 0 then
raise notice ''id is < 0!'';
some_row.descr := ''some other value'';
end if;
RETURN some_row;
END;
'
LANGUAGE 'plpgsql' VOLATILE;
CREATE TRIGGER trg_atest1
BEFORE INSERT
ON atest1
FOR EACH ROW
EXECUTE PROCEDURE test_trg();
Finally, firing the trigger like so:
insert into wfsys.atest1 values(123, 'some text');
Gives the following error:
ERROR: column "any_rec" does not exist
CONTEXT: PL/pgSQL function "test_trg" line 7 at select into variables
********** Error **********
ERROR: column "any_rec" does not exist
SQL state: 42703
Context: PL/pgSQL function "test_trg" line 7 at select into variables
It works great on 8.3 (my dev server), but throws the error on the machine I
am forced to work with, which is running version 7.4. I realize that
polymorphic functions were pretty new in v7.4, is there a workaround or am I
making a silly mistake? Or both?
Incidentally, I get the same error when I change the polymorphic function's
argument from "anyelement" to "wfsys.atest1" so it seems that it is occurring
in the trigger function. Thanks for any help.
Richard
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: polymorphic function in 7.4 vs. 8.3
@ 2009-06-11 20:39 Tom Lane <tgl@sss.pgh.pa.us>
parent: Richard Rosenberg <richrosenberg@earthlink.net>
0 siblings, 0 replies; 6+ messages in thread
From: Tom Lane @ 2009-06-11 20:39 UTC (permalink / raw)
To: Richard Rosenberg <richrosenberg@earthlink.net>; +Cc: pgsql-sql
Richard Rosenberg <richrosenberg@earthlink.net> writes:
> I have the following setup which works great in version 8.3 but throws an
> error in 7.4:
I think you mangled your example to the point where it doesn't work in
8.3 either ... I get
ERROR: a column definition list is required for functions returning "record"
CONTEXT: SQL statement "select * from dd_test( $1 )"
PL/pgSQL function "test_trg" line 7 at SQL statement
regards, tom lane
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: polymorphic function in 7.4 vs. 8.3
@ 2009-06-11 21:42 Richard Rosenberg <richrosenberg@earthlink.net>
0 siblings, 1 reply; 6+ messages in thread
From: Richard Rosenberg @ 2009-06-11 21:42 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; pgsql-sql
Tom, thanks for your prompt reply. I think I may have my head on straight now,
this should work:
CREATE TABLE atest1
(
id integer NOT NULL,
descr text,
CONSTRAINT atest1_pkey PRIMARY KEY (id)
);
CREATE OR REPLACE FUNCTION test1_trg()
RETURNS trigger AS
'
DECLARE
some_rec public.atest1;
BEGIN
some_rec.id := NEW.id;
some_rec.descr := NEW.descr;
select into some_rec * from dd_test(some_rec) as (id int4, descr text);
--some_rec := dd_test(some_rec);
RETURN some_rec;
END;
'
LANGUAGE 'plpgsql' VOLATILE;
CREATE OR REPLACE FUNCTION dd_test(anyelement)
RETURNS record AS
'
DECLARE
any_row alias for $1;
some_row record;
BEGIN
some_row := any_row;
if some_row.id < 0 then
raise notice ''id is < 0!'';
some_row.descr := ''some other value'';
end if;
RETURN some_row;
END;
'
LANGUAGE 'plpgsql' VOLATILE;
CREATE TRIGGER trg_atest1
BEFORE INSERT
ON atest1
FOR EACH ROW
EXECUTE PROCEDURE test1_trg();
insert into public.atest1 values(123, 'some text');
insert into public.atest1 values(-90, 'some text');
This gives the same result. Also in the trigger function test1_trg the syntax
of the call to the polymorphic function makes a difference in terms of the
error that is thrown. A simple assignment like:
. . .
some_rec := dd_test(some_rec);
. . .
Throws a syntax error, while a 'SELECT INTO. . .' like:
. . .
select into some_rec * from dd_test(some_rec) as (id int4, descr text);
. . .
Throws this:
ERROR: column "some_rec" does not exist
SQL state: 42703
Context: PL/pgSQL function "test1_trg" line 7 at select into variables
Sorry for the earlier typo(s), and thanks for any help.
Richard
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: polymorphic function in 7.4 vs. 8.3
@ 2009-06-11 21:49 Tom Lane <tgl@sss.pgh.pa.us>
parent: Richard Rosenberg <richrosenberg@earthlink.net>
0 siblings, 1 reply; 6+ messages in thread
From: Tom Lane @ 2009-06-11 21:49 UTC (permalink / raw)
To: Richard Rosenberg <richrosenberg@earthlink.net>; +Cc: pgsql-sql
Richard Rosenberg <richrosenberg@earthlink.net> writes:
> Tom, thanks for your prompt reply. I think I may have my head on straight now,
> this should work:
Yeah, but you're still out of luck on 7.4. Its plpgsql doesn't have any
ability to pass whole-row variables into expressions. I don't see any
answer for you except breaking down the row into columns, which of
course is going to be a huge notational PITA.
Sure you can't move the DB off 7.4? There would be pretty considerable
benefits from adopting some recent release instead.
regards, tom lane
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: polymorphic function in 7.4 vs. 8.3
@ 2009-06-11 23:02 Richard Rosenberg <richrosenberg@earthlink.net>
parent: Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 6+ messages in thread
From: Richard Rosenberg @ 2009-06-11 23:02 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-sql
On Thursday 11 June 2009 14:49:46 Tom Lane wrote:
> Sure you can't move the DB off 7.4? There would be pretty considerable
> benefits from adopting some recent release instead.
>
> regards, tom lane
Don't I know it. I am SOL as the machine is hosted/shared out by an external
provider. I can do it by getting rid of the polymorphism - breaking the
columns into separate args - as you say:
CREATE OR REPLACE FUNCTION public.test1_trg()
RETURNS "trigger" AS
'
DECLARE
some_rec public.atest1;
BEGIN
some_rec.id := NEW.id;
some_rec.descr := NEW.descr;
select into some_rec * from dd_test(some_rec.id, some_rec.descr, TG_RELNAME)
as (id int, descr text);
--some_rec := dd_test(some_rec)::public.atest1;
RETURN some_rec;
END;
'
LANGUAGE 'plpgsql' VOLATILE;
CREATE OR REPLACE FUNCTION public.dd_test(int, text, text)
RETURNS record AS
'
DECLARE
any_id alias for $1;
any_descr alias for $2;
tablename alias for $3;
some_id integer;
some_descr text;
some_row record;
BEGIN
some_id := any_id;
if some_id < 0 then
raise notice ''id is < 0!'';
some_descr := ''some other value'';
end if;
for some_row in execute ''select * from ''||tablename||'' where 1 = 0'' loop
end loop;
some_row.id := some_id;
some_row.descr := some_descr;
RETURN some_row;
END;
'
LANGUAGE 'plpgsql' VOLATILE;
Oh well, I'm glad I tested the approach out before going too far down this
road. Thanks again for your timely help.
Richard
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: polymorphic function in 7.4 vs. 8.3
@ 2009-06-11 23:16 Rob Sargent <robjsargent@gmail.com>
parent: Richard Rosenberg <richrosenberg@earthlink.net>
0 siblings, 0 replies; 6+ messages in thread
From: Rob Sargent @ 2009-06-11 23:16 UTC (permalink / raw)
To: ; +Cc: pgsql-sql
Richard Rosenberg wrote:
> On Thursday 11 June 2009 14:49:46 Tom Lane wrote:
>
>
>> Sure you can't move the DB off 7.4? There would be pretty considerable
>> benefits from adopting some recent release instead.
>>
>> regards, tom lane
>>
>
> Don't I know it. I am SOL as the machine is hosted/shared out by an external
> provider. I can do it by getting rid of the polymorphism - breaking the
> columns into separate args - as you say:
>
Hmm. Some subliminal coercion seems in order here. "Gee I hope it
doesn't leek out that <ThirdPartySupplier Here> is so poorly run that
they're stuck on postgres 7.4 with no hope of ever catching up with the
rest of the world"....
^ permalink raw reply [nested|flat] 6+ messages in thread
end of thread, other threads:[~2009-06-11 23:16 UTC | newest]
Thread overview: 6+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2009-06-11 20:16 polymorphic function in 7.4 vs. 8.3 Richard Rosenberg <richrosenberg@earthlink.net>
2009-06-11 20:39 ` Tom Lane <tgl@sss.pgh.pa.us>
2009-06-11 21:42 Re: polymorphic function in 7.4 vs. 8.3 Richard Rosenberg <richrosenberg@earthlink.net>
2009-06-11 21:49 ` Tom Lane <tgl@sss.pgh.pa.us>
2009-06-11 23:02 ` Richard Rosenberg <richrosenberg@earthlink.net>
2009-06-11 23:16 ` Rob Sargent <robjsargent@gmail.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox