pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedRe: Functions return a select in a table, which data type I
3+ messages / 2 participants
[nested] [flat]
* Functions return a select in a table, which data type I must use? (Brazilian User)
@ 2004-11-21 23:07 André Toscano <andre.toscano@uol.com.br>
2004-10-22 13:38 ` Re: Functions return a select in a table, which data type I Markus Bertheau <twanger@bluetwanger.de>
0 siblings, 1 reply; 3+ messages in thread
From: André Toscano @ 2004-11-21 23:07 UTC (permalink / raw)
To: pgsql-sql
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)
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Functions return a select in a table, which data type I
2004-11-21 23:07 Functions return a select in a table, which data type I must use? (Brazilian User) André Toscano <andre.toscano@uol.com.br>
@ 2004-10-22 13:38 ` Markus Bertheau <twanger@bluetwanger.de>
2004-10-29 09:08 ` Re: Functions return a select in a table, which data type I Markus Bertheau <twanger@bluetwanger.de>
0 siblings, 1 reply; 3+ messages in thread
From: Markus Bertheau @ 2004-10-22 13:38 UTC (permalink / raw)
To: André Toscano <andre.toscano@uol.com.br>; +Cc: pgsql-sql
В Пнд, 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 <twanger@bluetwanger.de>
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Functions return a select in a table, which data type I
2004-11-21 23:07 Functions return a select in a table, which data type I must use? (Brazilian User) André Toscano <andre.toscano@uol.com.br>
2004-10-22 13:38 ` Re: Functions return a select in a table, which data type I Markus Bertheau <twanger@bluetwanger.de>
@ 2004-10-29 09:08 ` Markus Bertheau <twanger@bluetwanger.de>
0 siblings, 0 replies; 3+ messages in thread
From: Markus Bertheau @ 2004-10-29 09:08 UTC (permalink / raw)
To: André Toscano <andre.toscano@uol.com.br>; +Cc: pgsql-sql
В Птн, 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_name
LANGUAGE '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>
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2004-11-21 23:07 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2004-11-21 23:07 Functions return a select in a table, which data type I must use? (Brazilian User) André Toscano <andre.toscano@uol.com.br>
2004-10-22 13:38 ` Markus Bertheau <twanger@bluetwanger.de>
2004-10-29 09:08 ` Markus Bertheau <twanger@bluetwanger.de>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox