Re: [SQL] Functions return a select in a table, which data type I

[Markus Bertheau]

Ð ÐÐÐ, 22.11.2004, Ð 00:07, Andrà Toscano ÐÐÑÐÑ:
> Hello, friends.
> 
> If anybody can help, how can I do a FUNCTION return a result from a 
> SELECT in a table in PostgreSQL?
> My Problem is the RETURN TYPE from a FUNCTION, I donÂt know what I have 
> to use to return the data as a select result.
> 
> Example:
> ----------------
> 
> DROP FUNCTION ACADEMICO.teste(int4);
> 
> CREATE FUNCTION ACADEMICO.teste(int4)
> RETURNS ?????
> AS
> '
>     select cod_aluno, nome, cpf from ACADEMICO.TB_alunos
> 
> '
> LANGUAGE 'SQL';

CREATE TYPE foo_type AS (cod_aluno TEXT, nome TEXT, cpf TEXT);
CREATE FUNCTION bar(int4)
        RETURNS SETOF foo_type
        LANGUAGE 'SQL'
        AS '
DECLARE
        var_rec foo_type;
BEGIN
        FOR var_rec IN SELECT cod_aluno, nome, cpf FROM table WHERE ...         LOOP
                RETURN NEXT var_rec;
        END LOOP;
        RETURN;
END;
';

-- 
Markus Bertheau <[EMAIL PROTECTED]>


---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
      subscribe-nomail command to [EMAIL PROTECTED] so that your
      message can get through to the mailing list cleanly