Thread: How to return a resultset/table from a sql function?

How to return a resultset/table from a sql function?

From
"Joost Kraaijeveld"
Date:
Hi,

Is it possible to return the following (parameterized) qyery from a sql or plpsql function, and if so, what is the
syntax?

SELECT{     (SELECT COUNT(klantnummer)         FROM abo_klt WHERE abonnement = $1),
        (SELECT SUM(aantal_abonnementen)     FROM abo_klt WHERE abonnement = $1));




Groeten,

Joost

Re: How to return a resultset/table from a sql function?

From
"Joost Kraaijeveld"
Date:
OK, to answer my own question (typo's possible, works here ;-)):


CREATE TYPE abonnementartikelheader AS  (col1 int4,  col2 int4);

CREATE OR REPLACE FUNCTION getabonnementartikelheader(int4)
RETURNS SETOF abonnementartikelheader AS
'
select
(SELECT COUNT(klantnummer)::int4       FROM abo_klt WHERE abonnement = $1),
(SELECT SUM(aantal_abonnementen)::int4 FROM abo_klt WHERE abonnement = $1);
'
LANGUAGE 'sql' VOLATILE;

Groeten,

Joost