FAQ
At the moment I am using tcl to experiment, but the problem is apparent even in the psql interpreter.

The problem is that the value returned from the function below lumps all of the attribute values together
in each row as opposed to keeping them separate. So instead of seeing three distinct attributes on each tuple,
I am just getting a single concatenated string with no attributes.

The example is below, and my question is: What is the proper way to return multiple tuples from a function so that
the attribute values are properly separated (available for use by an external program such as java/python/tcl)?

Help greatly appreciated! - Leon

Example follows:

I've got a table named contracts with three attributes: number integer, org_code text, type text

Here's the function in question:

create or replace function getContracts() returns setof contract as
$$
declare
r contract%rowtype;
begin
for r in select * from contract
loop
return next r;
end loop;
return;
end
$$
language plpgsql;

Here is the contrasting output seen in the psql interpreter:
===
GOOD ( attributes separated)
contracts=# select * from contract;
number | org_code | type
--------+----------+-------
1 | USGE | Renew
2 | USGE | Renew
3 | USGE | Renew

and via tcl - also good:
% set result [pg_exec $db "select * from contract"]
pgsql6.0
% pg_result $result -numTuples
3
% pg_result $result -numAttrs
3
===
BAD (attributes concatenated) why?
contracts=# select getContracts();
getcontracts
-----------------
(1,USGE,Renew)
(2,USGE,Renew)
(3,USGE,Renew)

and via tcl - not what I wanted:
% set result2 [pg_exec $db "select getContracts()"]
pgsql6.1
% pg_result $result2 -numTuples
3
% pg_result $result2 -numAttrs
1

Search Discussions

  • Tom Lane at Aug 5, 2010 at 4:21 pm

    Leon Starr writes:
    BAD (attributes concatenated) why?
    contracts=# select getContracts();
    getcontracts
    -----------------
    (1,USGE,Renew)
    (2,USGE,Renew)
    (3,USGE,Renew)
    This is expected. Try doing this if you want to "explode" the
    sub-columns:

    select * from getContracts();

    regards, tom lane
  • Leon Starr at Aug 5, 2010 at 4:54 pm
    Thanks, that does the trick and makes perfect sense. (I was eventually able to find a couple of threads on the same topic in this group). Less embarrassing since this is the 'novice' section!

    - Leon
    On Aug 5, 2010, at 9:20 AM, Tom Lane wrote:

    Leon Starr <leon_starr@modelint.com> writes:
    BAD (attributes concatenated) why?
    contracts=# select getContracts();
    getcontracts
    -----------------
    (1,USGE,Renew)
    (2,USGE,Renew)
    (3,USGE,Renew)
    This is expected. Try doing this if you want to "explode" the
    sub-columns:

    select * from getContracts();

    regards, tom lane

Related Discussions

Discussion Navigation
viewthread | post
Discussion Overview
grouppgsql-novice @
categoriespostgresql
postedJul 29, '10 at 10:38p
activeAug 5, '10 at 4:54p
posts3
users2
websitepostgresql.org
irc#postgresql

2 users in discussion

Leon Starr: 2 posts Tom Lane: 1 post

People

Translate

site design / logo © 2022 Grokbase