Thread: PL/Java (was: stored procedures)
> Function in Postgres can be written in PL/pgSQL (similar to PL/SQL), > PL/Perl, PL/Tcl, PL/Python, PL/sh, PL/R (similar to S-Plus statistical > language), and I think there might be a PL/Java available somewhere. I searched the list archives and Google - came up with some extensive discussions about PL/Java in August 2001, but it doesn'tlook like it ever got going. I would be very interested in contributing to a PL/Java project - is there anythinghappening right now? Roman
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&idoc=0&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 On Thu, 2003-03-13 at 14:04, Joe Conway wrote: > 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
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/<whatever> It would probably be better to have them point to the corresponding idocs pages. Joe
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 On Tue, 2003-03-25 at 05:49, ts wrote: > >>>>> "R" == Robert Treat <xzilla@users.sourceforge.net> writes: > > R> http://techdocs.postgresql.org/guides/Pgplmeta > > plruby is available for PostgreSQL >= 6 > > > > Guy Decoux > > > ---------------------------(end of broadcast)--------------------------- > TIP 3: if posting/reading through Usenet, please send an appropriate > subscribe-nomail command to majordomo@postgresql.org so that your > message can get through to the mailing list cleanly
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: > 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/<whatever> > > 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