PL/Java (was: stored procedures)

Roman Fail wrote:

I searched the list archives and Google - came up with some extensive
discussions about PL/Java in August 2001, but it doesn't look like it
ever got going. I would be very interested in contributing to a
PL/Java project - is there anything happening right now?

I'm not really sure -- that thread is probably the one I remembered.
Maybe someone else can chime in...

Joe

On Thu, 2003-03-13 at 13:31, Joe Conway wrote:

I'm not really sure -- that thread is probably the one I remembered.
Maybe someone else can chime in...

http://pljava.sourceforge.net/

BTW, you can also write stored procedures in Ruby, C, and SQL.

Cheers,

Neil

--
Neil Conway <neilc@samurai.com> || PGP Key ID: DB3C29FC

Neil Conway wrote:

http://pljava.sourceforge.net/

Interesting -- I did a quick search on sourceforge, but I guess it was a
bit too quick.

BTW, you can also write stored procedures in Ruby, C, and SQL.

I wasn't aware of Ruby, but I don't know how I could possibly have
forgotten to mention C and SQL!

I wonder if we shouldn't have a spot on techdocs with links to all of
the Postgres PLs all in one place? It makes for an impressive list; for
the record:

Available PostgreSQL Procedural Languages
==========================================
C
http://www.us.postgresql.org/users-lounge/docs/7.3/postgres/xfunc-c.html

SQL
http://www.us.postgresql.org/users-lounge/docs/7.3/postgres/xfunc-sql.html

PL/pgSQL
http://www.us.postgresql.org/users-lounge/docs/7.3/postgres/plpgsql.html

PL/Perl
http://www.us.postgresql.org/users-lounge/docs/7.3/postgres/plperl.html

PL/Tcl
http://www.us.postgresql.org/users-lounge/docs/7.3/postgres/pltcl.html

PL/Python
http://www.us.postgresql.org/users-lounge/docs/7.3/postgres/plpython.html

PL/sh
http://webmail.postgresql.org/~petere/plsh.html

PL/R
http://www.joeconway.com/plr/

PL/Java
http://pljava.sourceforge.net/

PL/Ruby
http://raa.ruby-lang.org/list.rhtml?name=pl-ruby

==========================================

Joe

My DBA and I looked at the link on using SQL for stored procedures
(thanks Neil!) and he raised a couple concerns:

1. In the example given, the query is directed at just one table; he
says he needs to join multiple tables, and have it return a set. Can PG
do this?

2. The docs say this ability to return a set is deprecated and may be
removed in a future release. Why is that? Has this functionality been
replaced by something else (better?)

Sorry if these are really dumb questions. We're trying to use pg
(instead of O or SS) but we're new to it.

TIA
Petre

Neil Conway wrote:
http://pljava.sourceforge.net/

Interesting -- I did a quick search on sourceforge, but I guess it was a
bit too quick.

BTW, you can also write stored procedures in Ruby, C, and SQL.

I wasn't aware of Ruby, but I don't know how I could possibly have
forgotten to mention C and SQL!

I wonder if we shouldn't have a spot on techdocs with links to all of
the Postgres PLs all in one place? It makes for an impressive list; for
the record:

Available PostgreSQL Procedural Languages
==========================================
C
http://www.us.postgresql.org/users-lounge/docs/7.3/postgres/xfunc-c.html

SQL
http://www.us.postgresql.org/users-lounge/docs/7.3/postgres/xfunc-sql.html

PL/pgSQL
http://www.us.postgresql.org/users-lounge/docs/7.3/postgres/plpgsql.html

PL/Perl
http://www.us.postgresql.org/users-lounge/docs/7.3/postgres/plperl.html

PL/Tcl
http://www.us.postgresql.org/users-lounge/docs/7.3/postgres/pltcl.html

PL/Python
http://www.us.postgresql.org/users-lounge/docs/7.3/postgres/plpython.html

PL/sh
http://webmail.postgresql.org/~petere/plsh.html

PL/R
http://www.joeconway.com/plr/

PL/Java
http://pljava.sourceforge.net/

PL/Ruby
http://raa.ruby-lang.org/list.rhtml?name=pl-ruby

==========================================

Joe

---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faqs/FAQ.html

Petre Scheie wrote:

My DBA and I looked at the link on using SQL for stored procedures
(thanks Neil!) and he raised a couple concerns:

1. In the example given, the query is directed at just one table; he
says he needs to join multiple tables, and have it return a set. Can PG
do this?

Pretty much any valid SQL statement will work. E.g.

CREATE TYPE ex1_tup AS (relname name, colname name);

CREATE OR REPLACE FUNCTION ex1(text) returns setof ex1_tup AS '
SELECT c.relname, a.attname
FROM pg_class c JOIN pg_attribute a ON a.attrelid = c.oid
WHERE relname = $1
' LANGUAGE 'sql';

regression=# SELECT * FROM ex1('pg_group');
relname | colname
----------+----------
pg_group | tableoid
pg_group | cmax
pg_group | xmax
pg_group | cmin
pg_group | xmin
pg_group | ctid
pg_group | groname
pg_group | grosysid
pg_group | grolist
(9 rows)

2. The docs say this ability to return a set is deprecated and may be
removed in a future release. Why is that? Has this functionality been
replaced by something else (better?)

That is referring to the older style of returning a set in the target
list. It would look like:

SELECT ex1('pg_group');

But that style has semantic problems, and in any case never really did
what you wanted (a single column could be returned, but a composite
return type just gave you back numeric pointers)

HTH,

Joe

On Thu, 13 Mar 2003, Petre Scheie wrote:

My DBA and I looked at the link on using SQL for stored procedures
(thanks Neil!) and he raised a couple concerns:

1. In the example given, the query is directed at just one table; he
says he needs to join multiple tables, and have it return a set. Can PG
do this?

2. The docs say this ability to return a set is deprecated and may be
removed in a future release. Why is that? Has this functionality been
replaced by something else (better?)

Sorry if these are really dumb questions. We're trying to use pg
(instead of O or SS) but we're new to it.

Sure, that's no problem. Has this guy read much of the docs yet? it's a
worthy investment of time, and they're quite good:

Main 7.3 docs page:

http://www.postgresql.org/docs/view.php?version=7.3&amp;idoc=0&amp;file=index.html

Here's some marketing bullet points from the programmer's guide in 7.3:

* User programmable and built-in functions
* User extensible types, as well as a rich variety of built-ins
* User definable operators
* User definable aggregates
* Rules
* Triggers
* Procedural Languages:
** PL/pgSQL
** PL/Tcl
** PL/Perl
** PL/Python
** PL/R (coming in 7.3)

It's the best database you've never heard of (before now). :-)

Neil,

I wonder if we shouldn't have a spot on techdocs with links to all of
the Postgres PLs all in one place? It makes for an impressive list; for
the record:

I'd put this up, but TECHDOCS/GUIDES appears to be down. Justin?

--
Josh Berkus
Aglio Database Solutions
San Francisco

Since people didn't like referencing all of these in the postgresql
docs, I used to add a notes at the bottom of the idocs, but I guess it
slipped off my radar. That being the case, I've added a page for this on
the techdocs site, available at
http://techdocs.postgresql.org/guides/Pgplmeta

If the language maintainers would be willing to look over the
information and make any corrections that would be great.

Robert Treat

Robert Treat wrote:

http://techdocs.postgresql.org/guides/Pgplmeta

If the language maintainers would be willing to look over the
information and make any corrections that would be great.

Looks good to me. One minor note -- the URLs that I gave in the original
email (and replicated on the above page) are to one specific mirror site:

http://www.us.postgresql.org/users-lounge/docs/7.3/postgres/&lt;whatever&gt;

It would probably be better to have them point to the corresponding
idocs pages.

Joe

"R" == Robert Treat <xzilla@users.sourceforge.net> writes:

R> http://techdocs.postgresql.org/guides/Pgplmeta

plruby is available for PostgreSQL >= 6

Guy Decoux

Thanks Guy. I've updated that info and swapped the links to the idocs
version instead of the mirror site as Joe suggested. Please note that if
you guys ever want to make a change there is an "edit this page" link at
the top so you don't have to wait on anyone.

Robert Treat

Hi!

I think we should not use the word "pl/java" since java is a trademark
of sun.
PL/pgJ is going to be distributed under Apache license. It is compatible
with 7.2+

Thanks,
Laszlo Hornyak

Joe Conway wrote: