Thread: function to return query result

Hi,

this is the first time for me to post into this mailing list so
let me introduce myself: I'm working at a little software company
in Germany. We run a mixed Linux/Windows network. The workhorse is
a Linux Server running named, apache, samba and last not least a
PostgresQL 7.0.2 service.

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

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

SELECT pms(); yields the error
NOTICE:  plpgsql: ERROR during compile of pms near line 2
ERROR:  parse error at or near "pmsummen"

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.

Peter.

PS: Are there any pgsql newsgroups? Has muc.lists.postgres.
question something in common with this mailing list?

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

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

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