Обсуждение: polymorphic function in 7.4 vs. 8.3

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

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

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

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

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

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