Thread: polymorphic function in 7.4 vs. 8.3

polymorphic function in 7.4 vs. 8.3

From

Richard Rosenberg

Date:

11 June 2009, 20:24:54

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 :=
''someother 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

Re: polymorphic function in 7.4 vs. 8.3

From

Tom Lane

Date:

11 June 2009, 20:39:42

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

Re: polymorphic function in 7.4 vs. 8.3

From

Tom Lane

Date:

11 June 2009, 21:49:58

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

Re: polymorphic function in 7.4 vs. 8.3

From

Richard Rosenberg

Date:

11 June 2009, 21:50:44

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

Re: polymorphic function in 7.4 vs. 8.3

From

Richard Rosenberg

Date:

11 June 2009, 23:02:54

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

Re: polymorphic function in 7.4 vs. 8.3

From

Rob Sargent

Date:

11 June 2009, 23:16:26

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