Thread: pl/Perl
Hello Few questions about pl/perl as the docs on this are very sparse.. (i find that with our procedural language docs in general) 1: can you call other stored procedures from within pl/Perl 2: from within a pl/Perl script , can i do a select etc.. i'm assuming no, because you cannot use DBI.. but just wondering if there is a way.. 3: installing it.. i installed postgres 7.0.3 from ports on my fbsd system. when i tried to install pl/perl i get this.. cd /usr/ports/databases/postgresql7/work/postgresql-7.0.2/src/pl/plperl/ perl Makefile.pl make "../../../src/Makefile.global", line 135: Need an operator "../../../src/Makefile.global", line 139: Missing dependency operator "../../../src/Makefile.global", line 143: Need an operator "../../../src/Makefile.global", line 144: Missing dependency operator "../../../src/Makefile.global", line 148: Need an operator "../../../src/Makefile.global", line 149: Need an operator "../../../src/Makefile.global", line 150: Need an operator "../../../src/Makefile.port", line 1: Need an operator "../../../src/Makefile.port", line 3: Need an operator "../../../src/Makefile.port", line 6: Need an operator "../../../src/Makefile.port", line 8: Need an operator "../../../src/Makefile.port", line 16: Need an operator "../../../src/Makefile.global", line 246: Missing dependency operator "../../../src/Makefile.global", line 247: Could not find ../../../src/Makefile.custom "../../../src/Makefile.global", line 248: Need an operator "../../../src/Makefile.global", line 253: Missing dependency operator "../../../src/Makefile.global", line 255: Need an operator "../../../src/Makefile.global", line 284: Missing dependency operator "../../../src/Makefile.global", line 286: Need an operator "../../../src/Makefile.global", line 288: Missing dependency operator "../../../src/Makefile.global", line 290: Need an operator "../../../src/Makefile.global", line 292: Missing dependency operator "../../../src/Makefile.global", line 294: Need an operator "../../../src/Makefile.global", line 296: Need an operator "../../../src/Makefile.global", line 299: Need an operator "../../../src/Makefile.global", line 301: Need an operator "../../../src/Makefile.global", line 304: Need an operator make: fatal errors encountered -- cannot continue any tips ? Jeff MacDonald, ----------------------------------------------------- PostgreSQL Inc | Hub.Org Networking Services jeff@pgsql.com | jeff@hub.org www.pgsql.com | www.hub.org 1-902-542-0713 | 1-902-542-3657 ----------------------------------------------------- Facsimile : 1 902 542 5386 IRC Nick : bignose PGP Public Key : http://bignose.hub.org/public.txt
On Wed, 21 Feb 2001, Jeff MacDonald wrote: > 1: can you call other stored procedures from within pl/Perl No. > 2: from within a pl/Perl script , can i do a select etc.. > i'm assuming no, because you cannot use DBI.. but just wondering > if there is a way.. Not currently. > 3: installing it.. i installed postgres 7.0.3 from ports on my fbsd system. > when i tried to install pl/perl i get this.. > > cd /usr/ports/databases/postgresql7/work/postgresql-7.0.2/src/pl/plperl/ > perl Makefile.pl > make Try using gmake instead of make (cd /usr/ports/devel/gmake, make) > > "../../../src/Makefile.global", line 135: Need an operator > "../../../src/Makefile.global", line 139: Missing dependency operator > "../../../src/Makefile.global", line 143: Need an operator > "../../../src/Makefile.global", line 144: Missing dependency operator > "../../../src/Makefile.global", line 148: Need an operator > "../../../src/Makefile.global", line 149: Need an operator > "../../../src/Makefile.global", line 150: Need an operator > "../../../src/Makefile.port", line 1: Need an operator > "../../../src/Makefile.port", line 3: Need an operator > "../../../src/Makefile.port", line 6: Need an operator > "../../../src/Makefile.port", line 8: Need an operator > "../../../src/Makefile.port", line 16: Need an operator > "../../../src/Makefile.global", line 246: Missing dependency operator > "../../../src/Makefile.global", line 247: Could not find ../../../src/Makefile.custom > "../../../src/Makefile.global", line 248: Need an operator > "../../../src/Makefile.global", line 253: Missing dependency operator > "../../../src/Makefile.global", line 255: Need an operator > "../../../src/Makefile.global", line 284: Missing dependency operator > "../../../src/Makefile.global", line 286: Need an operator > "../../../src/Makefile.global", line 288: Missing dependency operator > "../../../src/Makefile.global", line 290: Need an operator > "../../../src/Makefile.global", line 292: Missing dependency operator > "../../../src/Makefile.global", line 294: Need an operator > "../../../src/Makefile.global", line 296: Need an operator > "../../../src/Makefile.global", line 299: Need an operator > "../../../src/Makefile.global", line 301: Need an operator > "../../../src/Makefile.global", line 304: Need an operator > make: fatal errors encountered -- cannot continue > > any tips ? > > Jeff MacDonald, > > ----------------------------------------------------- > PostgreSQL Inc | Hub.Org Networking Services > jeff@pgsql.com | jeff@hub.org > www.pgsql.com | www.hub.org > 1-902-542-0713 | 1-902-542-3657 > ----------------------------------------------------- > Facsimile : 1 902 542 5386 > IRC Nick : bignose > PGP Public Key : http://bignose.hub.org/public.txt > >
> > 1: can you call other stored procedures from within pl/Perl > No. darn. > > > 2: from within a pl/Perl script , can i do a select etc.. > > i'm assuming no, because you cannot use DBI.. but just wondering > > if there is a way.. > Not currently. darn. > > 3: installing it.. i installed postgres 7.0.3 from ports on my fbsd system. > > when i tried to install pl/perl i get this.. > > > > cd /usr/ports/databases/postgresql7/work/postgresql-7.0.2/src/pl/plperl/ > > perl Makefile.pl > > make > Try using gmake instead of make (cd /usr/ports/devel/gmake, make) thanks ! works great. Jeff MacDonald, ----------------------------------------------------- PostgreSQL Inc | Hub.Org Networking Services jeff@pgsql.com | jeff@hub.org www.pgsql.com | www.hub.org 1-902-542-0713 | 1-902-542-3657 ----------------------------------------------------- Facsimile : 1 902 542 5386 IRC Nick : bignose PGP Public Key : http://bignose.hub.org/public.txt
FYI, My choice: if involving a lot of regular expressions, pl/Perl is better; if involving a lot of SQLs or other functions(or store procedures), then pl/pgsql is better. Jie LIANG St. Bernard Software Internet Products Inc. 10350 Science Center Drive Suite 100, San Diego, CA 92121 Office:(858)320-4873 jliang@ipinc.com www.stbernard.com www.ipinc.com On Wed, 21 Feb 2001, Jeff MacDonald wrote: > > > 1: can you call other stored procedures from within pl/Perl > > No. > > darn. > > > > > > 2: from within a pl/Perl script , can i do a select etc.. > > > i'm assuming no, because you cannot use DBI.. but just wondering > > > if there is a way.. > > Not currently. > > darn. > > > > 3: installing it.. i installed postgres 7.0.3 from ports on my fbsd system. > > > when i tried to install pl/perl i get this.. > > > > > > cd /usr/ports/databases/postgresql7/work/postgresql-7.0.2/src/pl/plperl/ > > > perl Makefile.pl > > > make > > Try using gmake instead of make (cd /usr/ports/devel/gmake, make) > > thanks ! works great. > > > Jeff MacDonald, > > ----------------------------------------------------- > PostgreSQL Inc | Hub.Org Networking Services > jeff@pgsql.com | jeff@hub.org > www.pgsql.com | www.hub.org > 1-902-542-0713 | 1-902-542-3657 > ----------------------------------------------------- > Facsimile : 1 902 542 5386 > IRC Nick : bignose > PGP Public Key : http://bignose.hub.org/public.txt >
Jie Liang <jliang@ipinc.com> writes:
> My choice:
> if involving a lot of regular expressions, pl/Perl is better;
> if involving a lot of SQLs or other functions(or store procedures),
> then pl/pgsql is better.
Also consider pltcl, which has pretty nearly perl-equivalent regexp
support, and can do queries too. Besides which it's easier to build/
install than plperl.
It's a shame that plperl doesn't yet have support for making queries.
It hasn't really progressed much past the proof-of-concept stage IMHO,
but no one is working on it :-(
regards, tom lane
Tom, I think one of system file has been crupted. I tried to drop some user then db is hangged there forever, so kill that session, when I relogin and type urldb=# \z categories NOTICE: get_groname: group 2 not found The connection to the server was lost. Attempting reset: Failed. !# and new user added cannot retrive data from any table. what I can do?? Jie LIANG St. Bernard Software Internet Products Inc. 10350 Science Center Drive Suite 100, San Diego, CA 92121 Office:(858)320-4873 jliang@ipinc.com www.stbernard.com www.ipinc.com On Wed, 21 Feb 2001, Tom Lane wrote: > Jie Liang <jliang@ipinc.com> writes: > > My choice: > > if involving a lot of regular expressions, pl/Perl is better; > > if involving a lot of SQLs or other functions(or store procedures), > > then pl/pgsql is better. > > Also consider pltcl, which has pretty nearly perl-equivalent regexp > support, and can do queries too. Besides which it's easier to build/ > install than plperl. > > It's a shame that plperl doesn't yet have support for making queries. > It hasn't really progressed much past the proof-of-concept stage IMHO, > but no one is working on it :-( > > regards, tom lane >
Tom,
I even cannot drop a user also,
urldb=# drop user sarah;
ERROR: DROP USER: group "manager" does not exist
urldb=#
urldb=# select * from pg_group; groname | grosysid | grolist
-------------+----------+------------------------------------manager | 5 | {1041,45,21}engineering |
3| {1041,1042,21,23,42,22}generic | 4 | {1009,1044,36,1045,1043}analysts | 2 |
{1048,1015,1004,1012,1014,45,1047}
urldb=# select * from pg_user where usename='sarah';usename | usesysid | usecreatedb | usetrace | usesuper | usecatupd
|
passwd | valuntil
---------+----------+-------------+----------+----------+-----------+----------+----------sarah | 1047 | f
| f | f | f |
******** |
but user sarah is not a memeber of manager at all!!
What I can do??
Jie LIANG
St. Bernard Software
Internet Products Inc.
10350 Science Center Drive
Suite 100, San Diego, CA 92121
Office:(858)320-4873
jliang@ipinc.com
www.stbernard.com
www.ipinc.com
On Wed, 21 Feb 2001, Tom Lane wrote:
> Jie Liang <jliang@ipinc.com> writes:
> > My choice:
> > if involving a lot of regular expressions, pl/Perl is better;
> > if involving a lot of SQLs or other functions(or store procedures),
> > then pl/pgsql is better.
>
> Also consider pltcl, which has pretty nearly perl-equivalent regexp
> support, and can do queries too. Besides which it's easier to build/
> install than plperl.
>
> It's a shame that plperl doesn't yet have support for making queries.
> It hasn't really progressed much past the proof-of-concept stage IMHO,
> but no one is working on it :-(
>
> regards, tom lane
>
another weired thing is, when I: select * from pg_group; it takes about 2 minites to return, it took just a tick before, it seems that some sys tables has been locked for a timeout. Thanks. Jie LIANG St. Bernard Software Internet Products Inc. 10350 Science Center Drive Suite 100, San Diego, CA 92121 Office:(858)320-4873 jliang@ipinc.com www.stbernard.com www.ipinc.com
Hi,
I'm trying to create Domain Aggregate function that mimic Access' Dlookup
function, but without any luck, after digging (i think) all the docs
don't have the solution yet.
Syntax:
DLookup ('field', 'table|view', ['condition'])
where:field -- column name or calculation table|view --condition (optional) -- SQL WHERE condition
withoutthe WHERE keywordIf the query returns multiple rows then it will only get thetopmost column.If
thequery retuns 0 rows then NULL would be returned
An example argument to the function would be
DLookup ('id', 'student', 'name=\'Bill Gates\'') --> '2001-432'
For now, I use some workaround for this, and it would be easier for me if
this function is present.
Hope u guys can help ;). Thank u in advance
Herbert
--
MSU - Iligan Institute of Technology
Iligan City, Philippines
On Tue, 27 Feb 2001, Herbert Ambos wrote:
> I'm trying to create Domain Aggregate function that mimic Access' Dlookup
> function, but without any luck, after digging (i think) all the docs
> don't have the solution yet.
>
> Syntax:
>
> DLookup ('field', 'table|view', ['condition'])
>
> where:
> field -- column name or calculation
> table|view --
> condition (optional) -- SQL WHERE condition without the
> WHERE keyword
>
> If the query returns multiple rows then it will only get the
> topmost column.
> If the query retuns 0 rows then NULL would be returned
>
>
> An example argument to the function would be
>
> DLookup ('id', 'student', 'name=\'Bill Gates\'') --> '2001-432'
Is this supposed to give effectively the same result as the subquery
(select student.id where name='Bill Gates' limit 1)? I don't think
that subquery is supported in 7.0, but will be in 7.1 along with
EXECUTE for plpgsql which should let you build a query out of the
parts you give it. I think you'd possibly be able to do this in 7.0
using pltcl but I don't know tcl so I can't help there.
Mr. Ambos, > Is this supposed to give effectively the same result as the subquery > (select student.id where name='Bill Gates' limit 1)? I don't think > that subquery is supported in 7.0, but will be in 7.1 along with > EXECUTE for plpgsql which should let you build a query out of the > parts you give it. I think you'd possibly be able to do this in 7.0 > using pltcl but I don't know tcl so I can't help there. Based on an earlier discussion regarding dynamic queries and EXECUTE, what you want will be hard to do with an SQL or PL/pgSQL function (although possible) even in 7.1. According to one developer, with PL/Tcl it's easier to build dynamic queries; one could also use C or Perl. However, you may want to re-think why you're using a dynamic "DLookup" function rather than a more specific function, saved query, or view. Dynamic queries are always going to run slower, regardless of language, than saved DB structures. This is especially true of Dlookup in MS Access, let alone DSum or DCount. If the program role you're trying to serve is frequent (student look-up, for example) in your application, you would probably be better off in several regards constructing a specific function to fill that purpose (lf_lookup_student_id(VARCHAR)). Personally, I'm a function "junkie" an I've never had need to replicate the VBA Dlookup functionality. -Josh Berkus -- ______AGLIO DATABASE SOLUTIONS___________________________ Josh Berkus Complete informationtechnology josh@agliodbs.com and data management solutions (415) 565-7293 for law firms, small businesses fax 621-2533 and non-profit organizations. San Francisco
I suspect you have a group with a dangling reference to a deleted user.
What do you get from "select * from pg_user" and "select * from
pg_group" ?
regards, tom lane
Tom,
1.Where or how I can get pltcl.so? I have not find this file anywhere in
my
source except a pltcl.c.
2.Dose installation same as plpgsql?
i.e.
CREATE FUNCTION pltcl_call_handler () RETURNS OPAQUE AS '/usr/local/pgsql/lib/pltcl.so' LANGUAGE 'C';
CREATE TRUSTED PROCEDURAL LANGUAGE 'pltcl' HANDLER pltcl_call_handler LANCOMPILER 'PL/pgtcl';
3.Where I can find more doc about pltcl?
Thanks for your time.
Jie LIANG
St. Bernard Software
10350 Science Center Drive
Suite 100, San Diego, CA 92121
Office:(858)320-4873
jliang@ipinc.com
www.stbernard.com
www.ipinc.com
On Wed, 21 Feb 2001, Tom Lane wrote:
> Jie Liang <jliang@ipinc.com> writes:
> > My choice:
> > if involving a lot of regular expressions, pl/Perl is better;
> > if involving a lot of SQLs or other functions(or store procedures),
> > then pl/pgsql is better.
>
> Also consider pltcl, which has pretty nearly perl-equivalent regexp
> support, and can do queries too. Besides which it's easier to build/
> install than plperl.
>
> It's a shame that plperl doesn't yet have support for making queries.
> It hasn't really progressed much past the proof-of-concept stage IMHO,
> but no one is working on it :-(
>
> regards, tom lane
>
On 3/14/01, 5:04:49 PM, Jie Liang <jliang@ipinc.com> wrote regarding Re: [SQL] pl/Perl : > Tom, I'm not the organ grinder, but maybe the monkey can help ;-) > 1.Where or how I can get pltcl.so? I have not find this file anywhere in > my > source except a pltcl.c. Did you set the relevant parameter with ./configure? Something like --with-tcl probably --help will give a list of options. > 2.Dose installation same as plpgsql? > i.e. > CREATE FUNCTION pltcl_call_handler () RETURNS OPAQUE AS > '/usr/local/pgsql/lib/pltcl.so' LANGUAGE 'C'; I just use the "createlang" command-line tool. > CREATE TRUSTED PROCEDURAL LANGUAGE 'pltcl' > HANDLER pltcl_call_handler > LANCOMPILER 'PL/pgtcl'; > 3.Where I can find more doc about pltcl? There's a bit in the user-manual (developer's version - 7.1 - anyway) on the website. Actually - there seems to be a break in the "Next" links in my local copy of the docs. Is that just me? Try a google search for tcl/tk - should turn up plenty of general stuff on the language. I don't use it myself, but it seems great for simple projects. - Richard Huxton > Thanks for your time. > Jie LIANG
Jie Liang wrote: > Tom, > > 1.Where or how I can get pltcl.so? I have not find this file anywhere in > my > source except a pltcl.c. > 2.Dose installation same as plpgsql? > i.e. > CREATE FUNCTION pltcl_call_handler () RETURNS OPAQUE AS > '/usr/local/pgsql/lib/pltcl.so' LANGUAGE 'C'; > CREATE TRUSTED PROCEDURAL LANGUAGE 'pltcl' > HANDLER pltcl_call_handler > LANCOMPILER 'PL/pgtcl'; No! Please switch to using the createlang shell script. > 3.Where I can find more doc about pltcl? Chapter 11 of the PostgreSQL users documentation. Jan -- #======================================================================# # It's easier to get forgiveness for being wrong than for being right. # # Let's break this rule - forgive me. # #================================================== JanWieck@Yahoo.com # _________________________________________________________ Do You Yahoo!? Get your free @yahoo.com address at http://mail.yahoo.com