COnsidering a move away from Postgres

Jason Tesser <jtesser@nbbc.edu> writes:

1. Our dev plan involves alot of stored procedures to be used and we have
found the way this is done in PG to be painful. (ie. To return multiple
record from different tables you have to define a type.

FWIW, this won't be essential any more in 8.1. See the examples in the
development documentation:
http://developer.postgresql.org/docs/postgres/xfunc-sql.html#XFUNC-OUTPUT-PARAMETERS
http://developer.postgresql.org/docs/postgres/plpgsql-declarations.html#PLPGSQL-DECLARATION-ALIASES
http://developer.postgresql.org/docs/postgres/plpgsql-control-structures.html#PLPGSQL-STATEMENTS-RETURNING

2. Also with stored procs it is painful to return mulitple records. The syntax
is more complicated than some other databases. (We are currently using
PL/SQL)

What's so hard about RETURN NEXT? What would you rather have?

3. The tools. PgAdmin does some things well but it is lacking the features of
some of the other gui tools.

I'm sure the pgAdmin guys would love having some more help.

regards, tom lane

HI

On Thursday 30 June 2005 9:20 am, Tom Lane wrote:

Jason Tesser <jtesser@nbbc.edu> writes:

1. Our dev plan involves alot of stored procedures to be used and we have
found the way this is done in PG to be painful. (ie. To return multiple
record from different tables you have to define a type.

FWIW, this won't be essential any more in 8.1. See the examples in the
development documentation:
http://developer.postgresql.org/docs/postgres/xfunc-sql.html#XFUNC-OUTPUT-P
ARAMETERS
http://developer.postgresql.org/docs/postgres/plpgsql-declarations.html#PLP
GSQL-DECLARATION-ALIASES
http://developer.postgresql.org/docs/postgres/plpgsql-control-structures.ht
ml#PLPGSQL-STATEMENTS-RETURNING

I might be missing it but how does this help me. What I would like is to be
able to return multiple records from a select statement that return multiple
columns from different tables without having to create a type. This is why
it is painful for us. The management of types is bad because as far as I
know there is no alter type and the depencies become a nightmane if you ever
need to change something.

<snip>

--
Jason Tesser
Developer for NMI
jtesser@nbbc.edu
Eph 2:8-10

On Thu, 2005-06-30 at 10:18 -0500, Jason Tesser wrote:

HI

On Thursday 30 June 2005 9:20 am, Tom Lane wrote:

Jason Tesser <jtesser@nbbc.edu> writes:

1. Our dev plan involves alot of stored procedures to be used and we have
found the way this is done in PG to be painful. (ie. To return multiple
record from different tables you have to define a type.

FWIW, this won't be essential any more in 8.1. See the examples in the
development documentation:
http://developer.postgresql.org/docs/postgres/xfunc-sql.html#XFUNC-OUTPUT-P
ARAMETERS
http://developer.postgresql.org/docs/postgres/plpgsql-declarations.html#PLP
GSQL-DECLARATION-ALIASES
http://developer.postgresql.org/docs/postgres/plpgsql-control-structures.ht
ml#PLPGSQL-STATEMENTS-RETURNING

I might be missing it but how does this help me. What I would like is to be
able to return multiple records from a select statement that return multiple
columns from different tables without having to create a type. This is why
it is painful for us. The management of types is bad because as far as I
know there is no alter type and the depencies become a nightmane if you ever
need to change something.

If I understand the new features correctly, rather than:
CREATE FUNCTION foo(i int) RETURNS custom_type AS ....
and custom_type is (int,text,text)
you will be able to do the following instead:
CREATE FUNCTION foo(IN i int, OUT x int, OUT y text, OUT z text) AS ...

As far as hard coding the OUT datatypes, if I understand the docs
correctly you can even:
CREATE FUNCTION foo(IN i int, OUT x anyelement, OUT y anyelement, OUT z
anyelement) AS ...

No custom type needed .. you specify how the output format in the
argument section itself.

Sven

Can this return multiples? I thought when you dfined columns dynamically like
your example it only returns one record and I need to be able to return a
set. Can your example return a set?

On Thursday 30 June 2005 10:58 am, Sven Willenberger wrote:

If I understand the new features correctly, rather than:
CREATE FUNCTION foo(i int) RETURNS custom_type AS ....
and custom_type is (int,text,text)
you will be able to do the following instead:
CREATE FUNCTION foo(IN i int, OUT x int, OUT y text, OUT z text) AS ...

As far as hard coding the OUT datatypes, if I understand the docs
correctly you can even:
CREATE FUNCTION foo(IN i int, OUT x anyelement, OUT y anyelement, OUT z
anyelement) AS ...

No custom type needed .. you specify how the output format in the
argument section itself.

Sven

--
Jason Tesser
Developer for NMI
jtesser@nbbc.edu
Eph 2:8-10

I've solved this for my case in 7.4 by defining a view with the desired column
layout and the return setof the view. This certainly depends on what you're
trying to accomplish.

On Thursday 30 June 2005 09:21 am, Jason Tesser wrote:

Can this return multiples? I thought when you dfined columns dynamically
like your example it only returns one record and I need to be able to
return a set. Can your example return a set?

On Thursday 30 June 2005 10:58 am, Sven Willenberger wrote:

If I understand the new features correctly, rather than:
CREATE FUNCTION foo(i int) RETURNS custom_type AS ....
and custom_type is (int,text,text)
you will be able to do the following instead:
CREATE FUNCTION foo(IN i int, OUT x int, OUT y text, OUT z text) AS ...

As far as hard coding the OUT datatypes, if I understand the docs
correctly you can even:
CREATE FUNCTION foo(IN i int, OUT x anyelement, OUT y anyelement, OUT z
anyelement) AS ...

No custom type needed .. you specify how the output format in the
argument section itself.

Sven

--
Open Source Solutions 4U, LLC 2570 Fleetwood Drive
Phone: +1 650 872 2425 San Bruno, CA 94066
Cell: +1 650 302 2405 United States
Fax: +1 650 872 2417

Tom Lane napisał(a):

Jason Tesser <jtesser@nbbc.edu> writes:

3. The tools. PgAdmin does some things well but it is lacking the features of
some of the other gui tools.

I'm sure the pgAdmin guys would love having some more help.

What about sqlmanager.net - it the best GUI with number of great features ?

See: http://www.sqlmanager.net/en/products/postgresql/manager

ML

There are very nice inexpensive alternatives to PG Admin III. Because
of it's cross platform nature it is severly lacking in many areas.

Check out PG Lightning Admin at:
http://www.amsoftwaredesign.com

Sven Willenberger <sven@dmv.com> writes:

As far as hard coding the OUT datatypes, if I understand the docs
correctly you can even:
CREATE FUNCTION foo(IN i int, OUT x anyelement, OUT y anyelement, OUT z
anyelement) AS ...

That exact example would not work --- anyelement/anyarray is all about
deducing output parameter types from input parameter types. So you need
at least one anyelement or anyarray input parameter. Here's a pretty
stupid example:

regression=# create function sum_n_prod (x anyelement, y anyelement,
regression(# OUT sum anyelement, OUT prod anyelement) as $$
regression$# begin
regression$# sum := x + y;
regression$# prod := x * y;
regression$# end$$ language plpgsql;
CREATE FUNCTION

This will work on any data type that has + and * operators. You can't
tell very easily in psql, but the first of these examples returns two
integers and the second returns two numeric columns:

regression=# select * from sum_n_prod(33,44);
sum | prod
-----+------
77 | 1452
(1 row)

regression=# select * from sum_n_prod(33.4,44.7);
sum | prod
------+---------
78.1 | 1492.98
(1 row)

I'm not entirely clear on exactly what problem Jason is concerned about,
but I don't think anyelement/anyarray will help him much. I do however
think that the out-parameter facility mostly fixes the specific
complaint of having to invent composite types just to return more than
one column.

regards, tom lane

Jason Tesser <jtesser@nbbc.edu> writes:

I might be missing it but how does this help me. What I would like is to be
able to return multiple records from a select statement that return multiple
columns from different tables without having to create a type.

You mean like this?

regression=# create table t1 (f1 int, f2 text);
CREATE TABLE
regression=# insert into t1 values(1, 'one');
INSERT 0 1
regression=# insert into t1 values(2, 'two');
INSERT 0 1
regression=# create table t2 (k1 int, k2 text);
CREATE TABLE
regression=# insert into t2 values(1, 'uno');
INSERT 0 1
regression=# insert into t2 values(2, 'dos');
INSERT 0 1
regression=# create function countem(lim int, out n int, out en text,
regression(# out es text) returns setof record as $$
regression$# declare r record;
regression$# begin
regression$# for r in select * from t1 join t2 on f1=k1 where f1 <= lim loop
regression$# n := r.f1;
regression$# en := r.f2;
regression$# es := r.k2;
regression$# return next;
regression$# end loop;
regression$# end$$ language plpgsql;
CREATE FUNCTION
regression=# select * from countem(2);
n | en | es
---+-----+-----
1 | one | uno
2 | two | dos
(2 rows)

regards, tom lane

On 6/30/05, Jason Tesser <jtesser@nbbc.edu> wrote:

I work for a college and we use PG currently as our main backend. We are
currently developing with Java. We are considering moving away from postgres
for the reasons I am going to list below. I would appreciate some thoughts
from the Postgres community on way we should or shouldn't leave postgres.

Out of curiosity, what other backends do you consider and what is their
syntax for such problems. Don't get me wrong, I don't intend to prove
anything by asking so. I am just curious what syntax would you prefer,
or in other words, what syntax is most convenient for a person doing
procedural language intense project. Hopefully it will help PL/pgSQL
develop in a best direction.

So, please post samples of syntax (and a DB-name, I'm curious about
other DBs syntaxes).

Regards,
Dawid

That is very similar to what I have been trying to do. I have 1
question and one problem though.

Question: DO I have to define every column I am returning as an out
going parameter?

Problem I ran your test and I am getting as error see below

test=# create function countum(lim int, out n int, out en text, out es
text) returns setof record as $$
test$# declare r record;
test$# begin test$# for r in select * from t1 join t2 on f1=k1 where f1
<= lim loop
test$# n := r.f1;
test$# en := r.f2;
test$# es := r.k2;
test$# return next;
test$# end loop; test$# end $$ language plpgsql;
ERROR: CREATE FUNCTION / OUT parameters are not implemented

-----Original Message-----
From: Tom Lane [mailto:tgl@sss.pgh.pa.us]
Sent: Thursday, June 30, 2005 3:57 PM
To: Jason Tesser
Cc: pgsql-general@postgresql.org
Subject: Re: [GENERAL] COnsidering a move away from Postgres

Jason Tesser <jtesser@nbbc.edu> writes:

I might be missing it but how does this help me. What I would like is

to be

able to return multiple records from a select statement that return

multiple

columns from different tables without having to create a type.

You mean like this?

regression=# create table t1 (f1 int, f2 text);
CREATE TABLE
regression=# insert into t1 values(1, 'one');
INSERT 0 1
regression=# insert into t1 values(2, 'two');
INSERT 0 1
regression=# create table t2 (k1 int, k2 text);
CREATE TABLE
regression=# insert into t2 values(1, 'uno');
INSERT 0 1
regression=# insert into t2 values(2, 'dos');
INSERT 0 1
regression=# create function countem(lim int, out n int, out en text,
regression(# out es text) returns setof record as $$
regression$# declare r record;
regression$# begin
regression$# for r in select * from t1 join t2 on f1=k1 where f1 <= lim
loop
regression$# n := r.f1;
regression$# en := r.f2;
regression$# es := r.k2;
regression$# return next;
regression$# end loop;
regression$# end$$ language plpgsql;
CREATE FUNCTION
regression=# select * from countem(2);
n | en | es
---+-----+-----
1 | one | uno
2 | two | dos
(2 rows)

regards, tom lane

OK I am an idiot you are running a cvs build I guess. Which at least
answers the problem.

"Jason Tesser" <JTesser@nbbc.edu> writes:

Problem I ran your test and I am getting as error see below

This is an 8.1 feature not something that exists in current releases.

regards, tom lane

Yes I figured it out could I bug one last time about my question :-)
Question: DO I have to define every column I am returning as an out
going parameter?
You have been helpful Tom and I really do appreciate it.

-----Original Message-----
From: Tom Lane [mailto:tgl@sss.pgh.pa.us]
Sent: Thursday, June 30, 2005 5:20 PM
To: Jason Tesser
Cc: pgsql-general@postgresql.org
Subject: Re: [GENERAL] COnsidering a move away from Postgres

"Jason Tesser" <JTesser@nbbc.edu> writes:

Problem I ran your test and I am getting as error see below

This is an 8.1 feature not something that exists in current releases.

regards, tom lane

"Jason Tesser" <JTesser@nbbc.edu> writes:

Question: DO I have to define every column I am returning as an out
going parameter?

Well, yes, where else is the system going to get the information?

regards, tom lane

Tony Caduto wrote:

There are very nice inexpensive alternatives to PG Admin III. Because
of it's cross platform nature it is severly lacking in many areas.

In what way does making software cross-plaform cause it to be 'lacking
in many areas'?

Check out PG Lightning Admin at:
http://www.amsoftwaredesign.com

Did you get chance to have a look at Kylix for compiling PG Lightning
under Linux natively?

http://www.borland.com/us/products/kylix/

--

Russ

Out of curiosity, what other backends do you consider and what is their
syntax for such problems.

Most folks that use Oracle's PL/SQL like it. I have a sneaking suspicion
Oracle used the GNAT parser for Ada as a starting point, but that is pure
conjecture. Oracle does document that PL/SQL is Ada with SQL extensions.
An enterprising individual could get the source for GNAT from AdaCore (it's
GPL'd) and create a workalike language for PostgreSQL. I'm not
enterprising.

Rick

pgsql-general-owner@postgresql.org wrote on 06/30/2005 04:03:49 PM:

On 6/30/05, Jason Tesser <jtesser@nbbc.edu> wrote:

I work for a college and we use PG currently as our main backend. We

are

currently developing with Java. We are considering moving away

from postgres

for the reasons I am going to list below. I would appreciate some

thoughts

from the Postgres community on way we should or shouldn't leave

postgres.

I have used PL/SQL for years. It's a great language that is easy to pick up
and offers lots of ability/promise. The syntax seems very easy for new
people to pick up who might know another language or are just starting out.
Of course the same can be said of Ada code. It's just very easy to read.

On 7/1/05, Richard_D_Levine@raytheon.com <Richard_D_Levine@raytheon.com>
wrote: