Thread: Functions return a select in a table, which data type I must use? (Brazilian User)

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';
----------------------------------

Thanks in Advance


André Toscano
(Brazilian user)

В Пнд, 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_typeLANGUAGE 'SQL'AS '
DECLAREvar_rec foo_type;
BEGINFOR var_rec IN SELECT cod_aluno, nome, cpf FROM table WHERE ...     LOOP    RETURN NEXT var_rec;END LOOP;RETURN;
END;
';

--
Markus Bertheau <twanger@bluetwanger.de>

В Птн, 22.10.2004, в 15:38, Markus Bertheau пишет:

> CREATE TYPE foo_type AS (cod_aluno TEXT, nome TEXT, cpf TEXT);
> CREATE FUNCTION bar(int4)
>     RETURNS SETOF foo_type
>     LANGUAGE 'SQL'
That should be LANGUAGE 'plpgsql'
>     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;
> ';

And if you want that function in SQL, there are two kinds of situations,
for which the solutions differ: If the record structure that the
function should return is the same as the structure of a table, you can
use the table name as the type. If this is not the case, you have to
create a custom type:

CREATE FUNCTION bar(int4)RETURNS table_name or custom_type_nameLANGUAGE 'SQL'AS '
SELECT whatever FROM table WHERE field = $1 AND foo;
END;
';

$1 is the value of the first argument.

--
Markus Bertheau <twanger@bluetwanger.de>