Received: from www-uk-05.digiserve.com ([195.172.23.63]) by mail.postgresql.org (8.11.1/8.11.1) with ESMTP id f28J4ux66224 for ; Thu, 8 Mar 2001 14:04:56 -0500 (EST) (envelope-from dev@archonet.com) Received: from mainbox.archonet.com (mwynhau.demon.co.uk [193.237.186.96]) by www-uk-05.digiserve.com (8.9.1/8.9.1) with ESMTP id TAA06937; Thu, 8 Mar 2001 19:06:05 GMT From: dev@archonet.com Received: from archonet.com (localhost.localdomain [127.0.0.1]) by mainbox.archonet.com (Postfix) with SMTP id 120762A478; Thu, 8 Mar 2001 18:55:46 +0000 (GMT) Date: Thu, 8 Mar 2001 18:55:46 -0000 To: "Boulat Khakimov" , Subject: Re: Writing SQL functions in Postgres X-Mailer: TWIG 2.6.2 In-Reply-To: <3AA7B6D4.C8BD7DB@inet-interactif.com> Message-Id: <20010308185546.120762A478@mainbox.archonet.com> X-Archive-Number: 200103/182 X-Sequence-Number: 1835 Boulat Khakimov said: > I want to write an SQL function in postgres that returns > row as a result. > > The problem is the select statement inside the funtion has > a two table join. So I dont know what to put after SETOF > > CREATE FUNCTION dummy() > RETURNS SETOF ????? > AS 'select a.name,b.cc > from tblusers a, > tbldocs b > where a.name=b.name' > LANGUAGE 'SQL'; > > > SETOF tblusers -- doesnt work > ERROR: function declared to return type tblusers does not retrieve > (tblusers.*) > > neither does SETOF tbldocs > > SETOF tblusers,tbldocs wont work either. There's good news and bad news. The good news is that if you define a view "tblboth" that selects from your two tables you can then do "returns setof tblboth". The bad news is that your function won't return a set of records - you'll get a list of OIDs (at least I think they're OIDs). Check the mailing archives for more on this. You can do your example with a simple view, but if you want a parameterised view you'll have to wait until 7.2 (I think it's on the todo list) - Richard Huxton