Обсуждение: Untyped result (setof / rowset) from Functions ?
Is there a technical reason why an untyped result (setof / rowset) from a function (sql or plpgsql) is not implemented ? Something Simple: CREATE FUNCTION foo(x int) RETURNS SETOF records AS 'SELECT t1.*, t2.* FROM t1 INNER JOIN t2 ON t1.a=t2a WHERE WHERE t1.id=$1' LANGUAGE SQL So I will not have to create a TYPE every time I return a SETOF JOINED tables or a sub-set of the columns of a table. Thanks.
Modern Mexican wrote: > Is there a technical reason why an untyped result (setof / rowset) from a > function (sql or plpgsql) is not implemented ? > > Something Simple: > > CREATE FUNCTION foo(x int) RETURNS SETOF records AS > 'SELECT t1.*, t2.* FROM t1 INNER JOIN t2 ON t1.a=t2a > WHERE WHERE t1.id=$1' > LANGUAGE SQL > > So I will not have to create a TYPE every time I return a SETOF JOINED > tables or a sub-set of the columns of a table. You can do this, but you have to specify what the type is when you call it. There needs to be *some* type defined somewhere. See the manuals - section 7.2.1.4 which gives the following example of specifying what results you expect from a dblink() call. SELECT * FROM dblink('dbname=mydb', 'select proname, prosrc from pg_proc') AS t1(proname name, prosrc text) WHERE proname LIKE 'bytea%'; -- Richard Huxton Archonet Ltd
thanks for the tip, but it would be nice if PG implemented it like MSSQL (very simple and straight forward). Felipe Sandoval Modern Mexican wrote: > Is there a technical reason why an untyped result (setof / rowset) from a > function (sql or plpgsql) is not implemented ? > > Something Simple: > > CREATE FUNCTION foo(x int) RETURNS SETOF records AS > 'SELECT t1.*, t2.* FROM t1 INNER JOIN t2 ON t1.a=t2a > WHERE WHERE t1.id=$1' > LANGUAGE SQL > > So I will not have to create a TYPE every time I return a SETOF JOINED > tables or a sub-set of the columns of a table. You can do this, but you have to specify what the type is when you call it. There needs to be *some* type defined somewhere. See the manuals - section 7.2.1.4 which gives the following example of specifying what results you expect from a dblink() call. SELECT * FROM dblink('dbname=mydb', 'select proname, prosrc from pg_proc') AS t1(proname name, prosrc text) WHERE proname LIKE 'bytea%'; -- Richard Huxton Archonet Ltd
Patches welcome. :) Seriously, if you were to come up with an interface that did this using a procedure language it would at a minimum be useful to others using dblink, and could potentially be brought into the core as well. Of course you could also work on a patch of the core, but if you're going to do that you should ask for pointers on -hackers first. On Wed, Oct 05, 2005 at 08:41:48AM -0700, Modern Mexican wrote: > thanks for the tip, but it would be nice if PG implemented it like MSSQL > (very simple and straight forward). > > Felipe Sandoval > > > Modern Mexican wrote: > > Is there a technical reason why an untyped result (setof / rowset) from a > > function (sql or plpgsql) is not implemented ? > > > > Something Simple: > > > > CREATE FUNCTION foo(x int) RETURNS SETOF records AS > > 'SELECT t1.*, t2.* FROM t1 INNER JOIN t2 ON t1.a=t2a > > WHERE WHERE t1.id=$1' > > LANGUAGE SQL > > > > So I will not have to create a TYPE every time I return a SETOF JOINED > > tables or a sub-set of the columns of a table. > > You can do this, but you have to specify what the type is when you call > it. There needs to be *some* type defined somewhere. > > See the manuals - section 7.2.1.4 which gives the following example of > specifying what results you expect from a dblink() call. > > SELECT * > FROM dblink('dbname=mydb', 'select proname, prosrc from pg_proc') > AS t1(proname name, prosrc text) > WHERE proname LIKE 'bytea%'; > > -- > Richard Huxton > Archonet Ltd > > > > ---------------------------(end of broadcast)--------------------------- > TIP 9: In versions below 8.0, the planner will ignore your desire to > choose an index scan if your joining column's datatypes do not > match > -- Jim C. Nasby, Sr. Engineering Consultant jnasby@pervasive.com Pervasive Software http://pervasive.com work: 512-231-6117 vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461