Обсуждение: problem with composed types in plpgsql

Hello

I play with new beta version (7.3b2). I trayed compose types created with
create type.

CREATE TYPE tf AS (f1 varchar(10), f2 varchar(10));

I wanted this type as returned type from plpgsql function. But I didn't
find how use this type in plpgsql. When I have function

CREATE OR REPLACE FUNCTION makesettf(integer) RETURNS SETOF tf AS '
  DECLARE f tf;
  BEGIN
    FOR i IN 1..$1 LOOP
      f.f1 := ''aaaaa'';
      f.f2 := ''bbbbb'';
      RETURN NEXT f;
    END LOOP;
    RETURN;
  END;
' LANGUAGE 'plpgsql';

After SELECT * FROM makesettf(10) I got message

psql:testfc.sql:17: WARNING:  plpgsql: ERROR during compile of makesettf
near line 6
psql:testfc.sql:17: ERROR:  Incorrect argument to RETURN NEXT at or near
"f".

Can I use in this version compose types?
Thank you
Pavel

PS. I can't use compose type in raise parameter too.

Hi,

There have been a few emails recently concerning using functions. However I
am a little confused as to their use with composite types. I can see how to
return a whole row from a table, but is it possible to return multiple
fields that do not originate from the same table?

Thanks

adam

Adam Witney wrote:
> There have been a few emails recently concerning using functions. However I
> am a little confused as to their use with composite types. I can see how to
> return a whole row from a table, but is it possible to return multiple
> fields that do not originate from the same table?

Sure. But you either need a named composite type that matches the row you want
to return, or you can use a record datatype and specify the column definitions
in the sql statement at run time.

A composite type exists for each table and view in your database, as well as
any stand-alone composite types you define. So, for example:

test=# create table foo (f1 int,f2 text);
CREATE TABLE
test=# create table bar (f3 int,f4 text);
CREATE TABLE
test=# create view foobar as select f1,f2,f4 from foo, bar where f1=f3;
CREATE VIEW
test=# insert into foo values(1,'a');
INSERT 1105496 1
test=# insert into foo values(2,'b');
INSERT 1105497 1
test=# insert into bar values(1,'c');
INSERT 1105498 1
test=# insert into bar values(2,'d');
INSERT 1105499 1

-- This uses a named composite type based on the view
test=# create function getfoobar1() returns setof foobar as 'select f1,f2,f4
from foo, bar where f1=f3' language sql;
CREATE FUNCTION
test=# select * from getfoobar1();
  f1 | f2 | f4
----+----+----
   1 | a  | c
   2 | b  | d
(2 rows)

-- This uses an anonymous composite type specified at runtime
test=# create function getfoobar2() returns setof record as 'select f1,f2,f4
from foo, bar where f1=f3' language sql;
CREATE FUNCTION
test=# select * from getfoobar2() as (f1 int,f2 text,f4 text);
  f1 | f2 | f4
----+----+----
   1 | a  | c
   2 | b  | d
(2 rows)

HTH,

Joe