Skip site navigation (1) Skip section navigation (2)

Returning arbitrary row sets from a function

From: "Gerard Mason" <gerardmason(at)hotmail(dot)com>
To: pgsql-novice(at)postgresql(dot)org
Subject: Returning arbitrary row sets from a function
Date: 2003-12-16 14:20:46
Message-ID: BAY7-F20S11aU4MbIcF0003b3c9@hotmail.com (view raw or flat)
Thread:
Lists: pgsql-novice
I would like to be able to deny client applications access to tables, and 
insulate the database through an api of functions. Updater, inserter and 
deleter functions look to be easy enough, but I am having a problem with 
rowset-returning functions: how do I declare return types that are a SETOF 
rows containing columns from an arbitrary query?

For example, suppose I want a function that returns a display-formatted 
organisation, by joining with countries so that the country appears as a 
name rather than an integer. What is currently happening is that the client 
is sending the query:

SELECT o.name, c.name AS country, o.phone, o.mobile, o.fax, o.email, 
o.website
FROM organisations o, countries c
WHERE c.id = o.country

What I'd *like* to happen is that the client calls, say, get_org_long() and 
retrieves the same data. But what would the return type be? The only way I 
can think to do it at the moment would be to create a view and return a 
setof that view's rowtype, but that seems to be using a sledgehammer to 
crack a nut. Also my first attempt at defining a test function that just 
returns rows from the organisations table fails with the message:

ERROR:  parser: parse error at or near "%"

It looks like this:

CREATE FUNCTION gems_test()
RETURNS SETOF organisations%ROWTYPE AS

That seems to be what the documentation is suggesting the return type should 
be (the examples are very incomplete!), but it doesn't work (7.1.3, and I 
can't upgrade without buying a new machine, which I don't want to do just 
yet).


Cheers,
Gerard.

_________________________________________________________________
Hotmail messages direct to your mobile phone http://www.msn.co.uk/msnmobile


Responses

pgsql-novice by date

Next:From: Tom LaneDate: 2003-12-16 14:28:04
Subject: Re: Returning arbitrary row sets from a function
Previous:From: Gerard MasonDate: 2003-12-16 14:18:54
Subject: Returning arbitrary row sets from a function

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group