Thread: Selects in server side functions
Hello,
I am trying to figure out how to return more than one field using "SETOF".
I can concatenate the fields in the select statement down to one and use SETOF Text to return them, then split them back apart in my code. This is a bit kludgy though. I would rather do this correctly and efficiently. In most RDBMS you can easily deal with multiple columns in server side procedures that contain a SELECT, but I can find nothing about how to do this in PostGreSQL Documentation or anywhere else.
How can you do this with PostGreSQL? If you can, is it better to do this this way, or better to put the query inline in the code?
Please respond to the email at the bottom.
thx,
Neil P Davis
Hi,
I'm getting this weird error when using the \d or \dd command. This same
error also pops up when I try to do anything in pgaccess. This is PostgreSQL
7.0.2 on LinuxPPC. I tried wiping out the whole installation, reinstalling,
rebooting the whole machine, starting with a fresh install and fresh
database. Still I can't seem to get this error to go away. I don't see
anything about this error in the manuals (I did a full text search!).
Here's a little transcript of the action. I created a database called test1,
a new table, then added a few rows. So it seems like I can hobble by as long
as I don't use pgaccess or the \d command with an argument ... but it would
be nice to use them (and know everything's right). Thanks for any help...
\/---------transcript:--------------\/
$ psql test1
Welcome to psql, the PostgreSQL interactive terminal.
Type: \copyright for distribution terms
\h for help with SQL commands
\? for help on internal slash commands
\g or terminate with semicolon to execute query
\q to quit
test1=# \d
List of relations
Name | Type | Owner
---------------+----------+----------
stuff | table | user1
stuff_sid_seq | sequence | user1
(2 rows)
test1=# \d stuff
ERROR: getattproperties: no attribute tuple 1259 -2
test1=# \dd stuff
ERROR: get_attdisbursion: no attribute tuple 16932 -2
test1=# insert into stuff (stuffhere) values ('here is some stuff.');
INSERT 19071 1
test1=# insert into stuff (stuffhere) values ('here is some more stuff.');
INSERT 19072 1
test1=# select * from stuff;
sid | stuffhere
-----+--------------------------
1 | here is some stuff.
2 | here is some more stuff.
(2 rows)
test1=# \d stuff
ERROR: getattproperties: no attribute tuple 1259 -2
"Neil Davis" <npdavis@hotmail.com> writes:
> I am trying to figure out how to return more than one field using "SETOF".
> I can concatenate the fields in the select statement down to one and
> use SETOF Text to return them, then split them back apart in my
> code.
SETOF is not for returning multiple columns, it is for returning
multiple *rows*. I don't think that's what you want here.
Returning multiple columns requires a tuple (structure) return datatype.
Unfortunately that's not supported all that well right now. Check the
pgsql archives for my message about functions returning tuples, from a
few days back.
regards, tom lane
On Fri, 27 Oct 2000, Neil Davis wrote: > Hello, > I am trying to figure out how to return more than one field using "SETOF". SETOF is to return more than one record, not more than one field. Also, SETOF is only currently working for SQL language functions. What you probably need is following: create procedure x returns foo .... and create a table foo with list of fields you want to return. That will work IFF you are not calling this function directly from the client, but are accessing it from plpgsql code. If you are accessing it from a client, it won't work. I don't know what will :) > I can concatenate the fields in the select statement down to one and use SETOF Text to return them, then split them backapart in my code. This is a bit kludgy though. I would rather do this correctly and efficiently. In most RDBMS you caneasily deal with multiple columns in server side procedures that contain a SELECT, but I can find nothing about how todo this in PostGreSQL Documentation or anywhere else. > > How can you do this with PostGreSQL? If you can, is it better to do this this way, or better to put the query inline inthe code? > > Please respond to the email at the bottom. > > thx, > Neil P Davis > npdavis@hotmail.com >
set digest pgsql-general
Isaac <Isaac@UNSTOPPABLE.com> writes:
> ERROR: getattproperties: no attribute tuple 1259 -2
Postgres 7.0.* doesn't work on PPC unless compiled -O0. The above is
a typical symptom of being compiled with higher optimization settings.
Unfortunately, it seems that our PPC RPMs for 7.0.2 were compiled with
the wrong -O level :-(. (Don't ask me why the RPMs ended up that way
when a clean source compilation uses -O0, but there it is.)
We'll try to do better with 7.0.3, but in the meantime try compiling
the source distribution from scratch.
FWIW, 7.1 will not have this weird problem with optimization on PPC.
regards, tom lane