Re: Function, that returns set of 2 tables columns

You need to specify and cast explicitly from your calling statement: SELECT
* FROM func1(10) AS (col1 smallint, col2 bigint, col3 date);

On 2/13/07, Dmitriy Chumack <saint(dot)d(dot)a(at)gmail(dot)com> wrote:
>
> Hi *
>
> I need to write a function, that returns a set of all columns from 2
> tables.
>
> e.g. I create such a function:
>
> CREATE OR REPLACE FUNCTION func(val_ int8)
> RETURNS SETOF record AS
> $BODY$
> DECLARE
> i record;
> BEGIN
>
> for i in select * from "Table1", "Table2"
> loop
> return next i;
> end loop;
>
> return;
>
> END;
> $BODY$
> LANGUAGE 'plpgsql' VOLATILE;
> ALTER FUNCTION func(val_ int8) OWNER TO postgres;
>
> But when I try to call it like this:
>
> SELECT * FROM func1(10);
>
> I have an error:
>
> ERROR: a column definition list is required for functions returning
> "record"
>
> This two tables have about 20 columns together, so I don't want list
> them each I call this function. Can I achieve this in some other
> (right) way?
>
> P.S. I don't want to create a specific type for this purpose, but
> if there is no other way, I should.
>
> Thanks in advance.
>
> --
> Best regards,
> Dmitriy Chumack mailto:saint(dot)d(dot)a(at)gmail(dot)com
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 9: In versions below 8.0, the planner will ignore your desire to
> choose an index scan if your joining column's datatypes do not
> match
>