function to return query result

* Peter Maas in "[GENERAL] function to return query result" dated
* 2000/11/29 12:34 wrote:

Hi,

Hi,

I tried to write a PL/PGSQL function that returns a query result:

I don't think you can do that yet, but let's look at what you've got.

CREATE FUNCTION pms() RETURNS pmsummen AS '
declare
result pmsummen;
BEGIN
select into result * from pmsummen;
return result;
END;
' LANGUAGE 'plpgsql';

Yeah, the return value of a function can't be a recordset.

I tried lots of variations of this function (employing PERFORM,
replacing pmsummen by record, ...) but everything failed. My first
intention was to write a parameterized view but this doesn't seem to
exist in PostgresQL so I tried to write a function returning a query
result. Thanks for your help.

Why don't you use a view? Something like:

create view test as select a.a, a.b, a.c, b.a, b.b
from a, b
where a.id=b.id;

Then you can do selects on the view using a where clause:

select *
from test
where a.a='somevalue';

--
ashley clark

Ashley Clark wrote:

Why don't you use a view? Something like:

create view test as select a.a, a.b, a.c, b.a, b.b
from a, b
where a.id=b.id;

Then you can do selects on the view using a where clause:

select *
from test
where a.a='somevalue';

Hi,

yes, but I wanted to encapsulate the more complicated data internals
(joins, sub queries, etc) in server functions. I could have used
the server functions or parameterized views in many places. Now if I
need the results e.g. in PHP, Java and a Windows program I have to
code and to maintain 3 functions performing the same task. Sometimes
a recordset evaluation needs more than one step with intermediate
temporary tables etc. You need a function for this.

Thanks for your help,

Peter.

--
------------------------------------------------------------------
Peter Maas, m+r infosysteme, D-52070 Aachen, Hubert-Wienen-Str. 24
Tel +49-241-875094 Fax +49-241-875095 eMail pm@mrinfo.de
------------------------------------------------------------------

Peter Maas wrote:

Then you can do selects on the view using a where clause:

select *
from test
where a.a='somevalue';

[...]

yes, but I wanted to encapsulate the more complicated data internals
(joins, sub queries, etc) in server functions.

OK, I can do that with views also but an advantage of parameterized
views and fucntions is that the SQL Parsing has to be done only once.

Peter

--
------------------------------------------------------------------
Peter Maas, m+r infosysteme, D-52070 Aachen, Hubert-Wienen-Str. 24
Tel +49-241-875094 Fax +49-241-875095 eMail pm@mrinfo.de
------------------------------------------------------------------