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