Thread: Stored Procedures

Hi all. I'm looking for a little help here. I have a 
project where I have to write some stored proceedures 
and am having some problems. My main issue is, I cannot 
figure out how to return a record set containing 
multipule columns. I am looking for a few examples on 
how I can do this. Most of what I have to do is fairly 
simple SQL queries based on a pramater sent to the 
function. I tried to use the SETOF <datatype> option, 
but only get back one column.

Any help will be would be greatly appricated. Simple 
examples would be of a great help.

Thanks,
Ben

On Tue, Oct 01, 2002 at 06:16:57PM +0000, bcschnei@attbi.com wrote:
> Hi all. I'm looking for a little help here. I have a 
> project where I have to write some stored proceedures 
> and am having some problems. My main issue is, I cannot 
> figure out how to return a record set containing 
> multipule columns. I am looking for a few examples on 
> how I can do this. Most of what I have to do is fairly 
> simple SQL queries based on a pramater sent to the 
> function. I tried to use the SETOF <datatype> option, 
> but only get back one column.

In 7.2 this is acomplished through returning a cursor from the function.
See the 7.3 documentation to see how to do that (AFAIK, this is not
documented in the 7.2 docs, although it does work).

In 7.3 you can return true record sets without the use of cursors. Again,
see the docs for 7.3 in the developers site.

-Roberto

-- 
+----|        Roberto Mello   -    http://www.brasileiro.net/  |------+
+       Computer Science Graduate Student, Utah State University      +
+       USU Free Software & GNU/Linux Club - http://fslc.usu.edu/     +
Q:    How many IBM CPU's does it take to do a logical right shift?
A:    33.  1 to hold the bits and 32 to push the register.

----- Original Message -----

From: bcschnei@attbi.com

Sent: Tuesday, October 01, 2002 4:25 PM

To: pgsql-sql@postgresql.org

Subject: [SQL] Stored Procedures

 

Hi all. I'm looking for a little help here. I have a
project where I have to write some stored proceedures
and am having some problems. My main issue is, I cannot
figure out how to return a record set containing
multipule columns. I am looking for a few examples on
how I can do this. Most of what I have to do is fairly
simple SQL queries based on a pramater sent to the
function. I tried to use the SETOF <datatype> option,
but only get back one column.

Any help will be would be greatly appricated. Simple
examples would be of a great help.

Thanks,
Ben


---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org

david williams wrote:
> Also,
>  
> the table definition MUST be in the Public Schema. I use my own schema 
> names but in order for the table to be found by the function it ( the 
> table ) must be in the public schema. Although it can be empty.

(Note: this discussion does not apply to PostgreSQL releases prior to 7.3 beta)

Not true. You need to be sure the schema the table is in is in your search 
path, or you need to fully qualify the table reference. See below for an example:

-- create a new schema
CREATE SCHEMA s1;
CREATE SCHEMA
-- change to the new schema
SET search_path='s1','$user','public';
SET
select current_schema(); current_schema
---------------- s1
(1 row)

-- create the table
CREATE TABLE foo (fooid int, foosubid int, fooname text);
CREATE TABLE
INSERT INTO foo VALUES(1,1,'Joe');
INSERT 794076 1
-- change back to public schema, but leave s1 in the search path
SET search_path='$user','public','s1';
SET
select current_schema(); current_schema
---------------- public
(1 row)

\dt        List of relations Schema | Name | Type  |  Owner
--------+------+-------+---------- s1     | foo  | table | postgres
(1 row)

CREATE FUNCTION getfoo(int) RETURNS foo AS '  SELECT * FROM foo WHERE fooid = $1;
' LANGUAGE SQL;
CREATE FUNCTION
\df getfoo                    List of functions Result data type | Schema |  Name  | Argument data types
------------------+--------+--------+--------------------- foo              | public | getfoo | integer
(1 row)

-- this will work
SELECT *, upper(fooname) FROM getfoo(1) AS t1; fooid | foosubid | fooname | upper
-------+----------+---------+-------     1 |        1 | Joe     | JOE
(1 row)

-- now try again with table name qualified in the function
DROP FUNCTION getfoo(int);
DROP FUNCTION
-- remove s1 from the search path
SET search_path='$user','public';
SET
select current_schema(); current_schema
---------------- public
(1 row)

\dt
No relations found.
CREATE FUNCTION getfoo(int) RETURNS s1.foo AS '  SELECT * FROM s1.foo WHERE fooid = $1;
' LANGUAGE SQL;
CREATE FUNCTION
\df getfoo                    List of functions Result data type | Schema |  Name  | Argument data types
------------------+--------+--------+--------------------- s1.foo           | public | getfoo | integer
(1 row)

-- this will work
SELECT *, upper(fooname) FROM getfoo(1) AS t1; fooid | foosubid | fooname | upper
-------+----------+---------+-------     1 |        1 | Joe     | JOE
(1 row)

HTH,

Joe

Ok, if this does not apply to versions prior to 7.3beta 
then what do I need to do if I am running 7.2.1? When I 
try to use the SETOF to retrun a row set, I only get 
one column.

Do I need to update Postgres to get things to work?

Ben
> david williams wrote:
> > Also,
> >  
> > the table definition MUST be in the Public Schema. I use my own schema 
> > names but in order for the table to be found by the function it ( the 
> > table ) must be in the public schema. Although it can be empty.
> 
> (Note:
>   this discussion does not apply to PostgreSQL releases prior to 7.3 beta)
> 
> Not true. You need to be sure the schema the table is in is in your search 
> path, or you need to fully qualify the table reference. See below for an 
> example:
> 
> -- create a new schema
> CREATE SCHEMA s1;
> CREATE SCHEMA
> -- change to the new schema
> SET search_path='s1','$user','public';
> SET
> select current_schema();
>   current_schema
> ----------------
>   s1
> (1 row)
> 
> -- create the table
> CREATE TABLE foo (fooid int, foosubid int, fooname text);
> CREATE TABLE
> INSERT INTO foo VALUES(1,1,'Joe');
> INSERT 794076 1
> -- change back to public schema, but leave s1 in the search path
> SET search_path='$user','public','s1';
> SET
> select current_schema();
>   current_schema
> ----------------
>   public
> (1 row)
> 
> \dt
>          List of relations
>   Schema | Name | Type  |  Owner
> --------+------+-------+----------
>   s1     | foo  | table | postgres
> (1 row)
> 
> CREATE FUNCTION getfoo(int) RETURNS foo AS '
>    SELECT * FROM foo WHERE fooid = $1;
> ' LANGUAGE SQL;
> CREATE FUNCTION
> \df getfoo
>                      List of functions
>   Result data type | Schema |  Name  | Argument data types
> ------------------+--------+--------+---------------------
>   foo              | public | getfoo | integer
> (1 row)
> 
> -- this will work
> SELECT *, upper(fooname) FROM getfoo(1) AS t1;
>   fooid | foosubid | fooname | upper
> -------+----------+---------+-------
>       1 |        1 | Joe     | JOE
> (1 row)
> 
> -- now try again with table name qualified in the function
> DROP FUNCTION getfoo(int);
> DROP FUNCTION
> -- remove s1 from the search path
> SET search_path='$user','public';
> SET
> select current_schema();
>   current_schema
> ----------------
>   public
> (1 row)
> 
> \dt
> No relations found.
> CREATE FUNCTION getfoo(int) RETURNS s1.foo AS '
>    SELECT * FROM s1.foo WHERE fooid = $1;
> ' LANGUAGE SQL;
> CREATE FUNCTION
> \df getfoo
>                      List of functions
>   Result data type | Schema |  Name  | Argument data types
> ------------------+--------+--------+---------------------
>   s1.foo           | public | getfoo | integer
> (1 row)
> 
> -- this will work
> SELECT *, upper(fooname) FROM getfoo(1) AS t1;
>   fooid | foosubid | fooname | upper
> -------+----------+---------+-------
>       1 |        1 | Joe     | JOE
> (1 row)
> 
> HTH,
> 
> Joe
> 

----- Original Message -----

From: bcschnei@attbi.com

Sent: Wednesday, October 02, 2002 1:53 PM

To: Joe Conway

Cc: david williams; pgsql-sql@postgresql.org

Subject: Re: [SQL] Stored Procedures

 

Ok, if this does not apply to versions prior to 7.3beta
then what do I need to do if I am running 7.2.1? When I
try to use the SETOF to retrun a row set, I only get
one column.

Do I need to update Postgres to get things to work?

Ben
> david williams wrote:
> > Also,
> > 
> > the table definition MUST be in the Public Schema. I use my own schema
> > names but in order for the table to be found by the function it ( the
> > table ) must be in the public schema. Although it can be empty.
>
> (Note:
>   this discussion does not apply to PostgreSQL releases prior to 7.3 beta)
>
> Not true. You need to be sure the schema the table is in is in your search
> path, or you need to fully qualify the table reference. See below for an
> example:
>
> -- create a new schema
> CREATE SCHEMA s1;
> CREATE SCHEMA
> -- change to the new schema
> SET search_path='s1','$user','public';
> SET
> select current_schema();
>   current_schema
> ----------------
>   s1
> (1 row)
>
> -- create the table
> CREATE TABLE foo (fooid int, foosubid int, fooname text);
> CREATE TABLE
> INSERT INTO foo VALUES(1,1,'Joe');
> INSERT 794076 1
> -- change back to public schema, but leave s1 in the search path
> SET search_path='$user','public','s1';
> SET
> select current_schema();
>   current_schema
> ----------------
>   public
> (1 row)
>
> \dt
>          List of relations
>   Schema | Name | Type  |  Owner
> --------+------+-------+----------
>   s1     | foo  | table | postgres
> (1 row)
>
> CREATE FUNCTION getfoo(int) RETURNS foo AS '
>    SELECT * FROM foo WHERE fooid = $1;
> ' LANGUAGE SQL;
> CREATE FUNCTION
> \df getfoo
>                      List of functions
>   Result data type | Schema |  Name  | Argument data types
> ------------------+--------+--------+---------------------
>   foo              | public | getfoo | integer
> (1 row)
>
> -- this will work
> SELECT *, upper(fooname) FROM getfoo(1) AS t1;
>   fooid | foosubid | fooname | upper
> -------+----------+---------+-------
>       1 |        1 | Joe     | JOE
> (1 row)
>
> -- now try again with table name qualified in the function
> DROP FUNCTION getfoo(int);
> DROP FUNCTION
> -- remove s1 from the search path
> SET search_path='$user','public';
> SET
> select current_schema();
>   current_schema
> ----------------
>   public
> (1 row)
>
> \dt
> No relations found.
> CREATE FUNCTION getfoo(int) RETURNS s1.foo AS '
>    SELECT * FROM s1.foo WHERE fooid = $1;
> ' LANGUAGE SQL;
> CREATE FUNCTION
> \df getfoo
>                      List of functions
>   Result data type | Schema |  Name  | Argument data types
> ------------------+--------+--------+---------------------
>   s1.foo           | public | getfoo | integer
> (1 row)
>
> -- this will work
> SELECT *, upper(fooname) FROM getfoo(1) AS t1;
>   fooid | foosubid | fooname | upper
> -------+----------+---------+-------
>       1 |        1 | Joe     | JOE
> (1 row)
>
> HTH,
>
> Joe
>

bcschnei@attbi.com wrote:
> Ok, if this does not apply to versions prior to 7.3beta 
> then what do I need to do if I am running 7.2.1? When I 
> try to use the SETOF to retrun a row set, I only get 
> one column.

First, prior to 7.3 there is no SCHEMA support in Postgres. Everything lives 
in essentially one and the same schema.

In 7.2.x and before, returning a composite type (i.e. multiple columns) gives 
you back one column of pointers (large integer values) to the actual row of 
data. You can access the individual columns, but it's ugly:

test=# CREATE TABLE foo (fooid int, foosubid int, fooname text);
CREATE
test=# INSERT INTO foo VALUES(1,1,'Joe');
INSERT 304822 1
test=# CREATE FUNCTION getfoo(int) RETURNS foo AS '
test'#   SELECT * FROM foo WHERE fooid = $1;
test'# ' LANGUAGE SQL;
CREATE
test=# select fooid(getfoo(1)), foosubid(getfoo(1)), fooname(getfoo(1)); fooid | foosubid | fooname
-------+----------+---------     1 |        1 | Joe
(1 row)

Joe

Hi,

I have a complex SQL query which requires the joining of 18 tables. There
are only primary key indices on the table and at the moment it runs a little
slow (30s or so) and so I am trying to optimise it.

The output of EXPLAIN is a little confusing and seems to vary from run to
run. Does the query optimiser have trouble with larger number of table
joins?

Also this will be running from a web front end, and I hope to have it
encapsulated all within a function. Would it be better to break it up into
multiple SQL statements/functions? Or to try to really tweak the indices?

Thanks for any advice

adam


-- 
This message has been scanned for viruses and
dangerous content by MailScanner, and is
believed to be clean.

Adam Witney <awitney@sghms.ac.uk> writes:
> I have a complex SQL query which requires the joining of 18 tables. There
> are only primary key indices on the table and at the moment it runs a little
> slow (30s or so) and so I am trying to optimise it.

> The output of EXPLAIN is a little confusing and seems to vary from run to
> run. Does the query optimiser have trouble with larger number of table
> joins?

The output probably would vary, because at that number of tables it'll
be using the GEQO optimizer, which is probabilistic.  If you don't like
that, you can raise the GEQO threshold above 18 tables, but I suspect
you'll not like the amount of time the exhaustive optimizer will take.

A reasonable solution is to jack up the threshold, experiment until you
find a good query plan, and then restructure the query with explicit
JOIN operators to limit the optimizer's search space.  That will bring
the planning time down out of the stratosphere.

See
http://www.ca.postgresql.org/users-lounge/docs/7.2/postgres/explicit-joins.html
for details.
        regards, tom lane