pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedplpgsql
23+ messages / 15 participants
[nested] [flat]
* plpgsql
@ 2000-12-08 20:51 Jie Liang <jliang@ipinc.com>
2000-12-12 06:42 ` Re: plpgsql Kovacs Zoltan Sandor <tip@pc10.radnoti-szeged.sulinet.hu>
0 siblings, 1 reply; 23+ messages in thread
From: Jie Liang @ 2000-12-08 20:51 UTC (permalink / raw)
To: ; +Cc: pgsql-sql
>
Hi, there,
Is there any way to handle exception ( such as cannot insert duplicate key on
a unique index) in
plpgsql function?
I don't want it abort whole transaction instead I want to do something else if
it happened,
but I don't want to use a select stmt first to waste the time.
In Orcale, in plsql we can say,
declare
begin
do something
exception
do something else
end;
How to this exception section in plpgsql????
--
Jie LIANG
Internet Products Inc.
10350 Science Center Drive
Suite 100, San Diego, CA 92121
Office:(858)320-4873
jliang@ipinc.com
www.ipinc.com
^ permalink raw reply [nested|flat] 23+ messages in thread
* Re: plpgsql
2000-12-08 20:51 plpgsql Jie Liang <jliang@ipinc.com>
@ 2000-12-12 06:42 ` Kovacs Zoltan Sandor <tip@pc10.radnoti-szeged.sulinet.hu>
2000-12-12 18:14 ` Re: plpgsql Jie Liang <jliang@jliang.ipinc.com>
0 siblings, 1 reply; 23+ messages in thread
From: Kovacs Zoltan Sandor @ 2000-12-12 06:42 UTC (permalink / raw)
To: Jie Liang <jliang@ipinc.com>; +Cc: pgsql-sql
> Hi, there,
>
> Is there any way to handle exception ( such as cannot insert duplicate key on
> a unique index) in
> plpgsql function?
>
> I don't want it abort whole transaction instead I want to do something else if
> it happened,
> but I don't want to use a select stmt first to waste the time.
Bad news: there is no such statement in PLPGSQL you like. My usual way to
do this is the same you wrote (SELECT first, if no rows FOUND, do the
INSERT).
Zoltan
^ permalink raw reply [nested|flat] 23+ messages in thread
* Re: plpgsql
2000-12-08 20:51 plpgsql Jie Liang <jliang@ipinc.com>
2000-12-12 06:42 ` Re: plpgsql Kovacs Zoltan Sandor <tip@pc10.radnoti-szeged.sulinet.hu>
@ 2000-12-12 18:14 ` Jie Liang <jliang@jliang.ipinc.com>
0 siblings, 0 replies; 23+ messages in thread
From: Jie Liang @ 2000-12-12 18:14 UTC (permalink / raw)
To: Kovacs Zoltan Sandor <tip@pc10.radnoti-szeged.sulinet.hu>; +Cc: pgsql-sql
OO,
That's a big disadvantage, because if the table is huge, using select stmt
walking even on an index will take some time and duplicate occur not
often, efficiency is a big problem.
Thanks anyway.
Jie LIANG
Internet Products Inc.
10350 Science Center Drive
Suite 100, San Diego, CA 92121
Office:(858)320-4873
jliang@ipinc.com
www.ipinc.com
On Tue, 12 Dec 2000, Kovacs Zoltan Sandor wrote:
> > Hi, there,
> >
> > Is there any way to handle exception ( such as cannot insert duplicate key on
> > a unique index) in
> > plpgsql function?
> >
> > I don't want it abort whole transaction instead I want to do something else if
> > it happened,
> > but I don't want to use a select stmt first to waste the time.
> Bad news: there is no such statement in PLPGSQL you like. My usual way to
> do this is the same you wrote (SELECT first, if no rows FOUND, do the
> INSERT).
>
> Zoltan
>
^ permalink raw reply [nested|flat] 23+ messages in thread
* plpgsql
@ 2000-12-09 08:16 feblec <carvalho@ig.com.br>
2000-12-12 16:20 ` Re: plpgsql Oliver Elphick <olly@lfix.co.uk>
2000-12-12 16:56 ` Re: plpgsql Mike Castle <dalgoda@ix.netcom.com>
0 siblings, 2 replies; 23+ messages in thread
From: feblec @ 2000-12-09 08:16 UTC (permalink / raw)
To: pgsql-sql
consultas=# CREATE FUNCTION spread(text)
consultas-# RETURNS text
consultas-# AS 'DECLARE
consultas'# str text;
consultas'# ret text;
consultas'# i integer;
consultas'# len integer;
consultas'# BEGIN
consultas'# str := upper($1);
consultas'# ret := ''''; -- start with zero length
consultas'# i := 1;
consultas'# len := length(str);
consultas'# WHILE i <= len LOOP
consultas'# ret := ret || substr(str, i, 1) || '' '';
consultas'# i := i + 1;
consultas'# END LOOP;
consultas'# RETURN ret;
consultas'# END;'
consultas-# LANGUAGE 'plpgsql';
ERROR: Unrecognized language specified in a CREATE
FUNCTION: 'plpgsql'. Recognized languages are sql, C, internal and the
created procedural languages.
consultas=# select version();
version
-------------------------------------------------------------
PostgreSQL 7.0.3 on i686-pc-linux-gnu, compiled by gcc 2.96
(1 row)
--
Fernando Eduardo B. L. e Carvalho
Development/Consultant
^ permalink raw reply [nested|flat] 23+ messages in thread
* Re: plpgsql
2000-12-09 08:16 plpgsql feblec <carvalho@ig.com.br>
@ 2000-12-12 16:20 ` Oliver Elphick <olly@lfix.co.uk>
1 sibling, 0 replies; 23+ messages in thread
From: Oliver Elphick @ 2000-12-12 16:20 UTC (permalink / raw)
To: feblec <carvalho@ig.com.br>; +Cc: pgsql-sql
feblec wrote:
>consultas=# CREATE FUNCTION spread(text)
...
>consultas-# LANGUAGE 'plpgsql';
>ERROR: Unrecognized language specified in a CREATE
>FUNCTION: 'plpgsql'. Recognized languages are sql, C, internal and the
>created procedural languages.
create function plpgsql_call_handler() returns opaque
as '/usr/local/pgsql/lib/plpgsql.so' <-- or wherever
language 'C';
create trusted procedural language 'plpgsql'
handler plpgsql_call_handler
lancompiler 'PL/pgSQL';
--
Oliver Elphick Oliver.Elphick@lfix.co.uk
Isle of Wight http://www.lfix.co.uk/oliver
PGP: 1024R/32B8FAA1: 97 EA 1D 47 72 3F 28 47 6B 7E 39 CC 56 E4 C1 47
GPG: 1024D/3E1D0C1C: CA12 09E0 E8D5 8870 5839 932A 614D 4C34 3E1D 0C1C
========================================
"Be of good courage, and he shall strengthen your
heart, all ye that hope in the LORD."
Psalms 31:24
^ permalink raw reply [nested|flat] 23+ messages in thread
* Re: plpgsql
2000-12-09 08:16 plpgsql feblec <carvalho@ig.com.br>
@ 2000-12-12 16:56 ` Mike Castle <dalgoda@ix.netcom.com>
1 sibling, 0 replies; 23+ messages in thread
From: Mike Castle @ 2000-12-12 16:56 UTC (permalink / raw)
To: pgsql-sql
On Sat, Dec 09, 2000 at 08:16:06AM +0000, feblec wrote:
> FUNCTION: 'plpgsql'. Recognized languages are sql, C, internal and the
> created procedural languages.
Look at: postgresql-7.0.3/src/pl/plpgsql/enable_plpgsql
mrc
--
Mike Castle Life is like a clock: You can work constantly
dalgoda@ix.netcom.com and be right all the time, or not work at all
www.netcom.com/~dalgoda/ and be right at least twice a day. -- mrc
We are all of us living in the shadow of Manhattan. -- Watchmen
^ permalink raw reply [nested|flat] 23+ messages in thread
* Plpgsql
@ 2000-12-12 13:30 feblec <carvalho@ig.com.br>
0 siblings, 0 replies; 23+ messages in thread
From: feblec @ 2000-12-12 13:30 UTC (permalink / raw)
To: pgsql-sql
consultas=# CREATE FUNCTION spread(text)
consultas-# RETURNS text
consultas-# AS 'DECLARE
consultas'# str text;
consultas'# ret text;
consultas'# i integer;
consultas'# len integer;
consultas'# BEGIN
consultas'# str := upper($1);
consultas'# ret := ''''; -- start with zero length
consultas'# i := 1;
consultas'# len := length(str);
consultas'# WHILE i <= len LOOP
consultas'# ret := ret || substr(str, i, 1) || '' '';
consultas'# i := i + 1;
consultas'# END LOOP;
consultas'# RETURN ret;
consultas'# END;'
consultas-# LANGUAGE 'plpgsql';
ERROR: Unrecognized language specified in a CREATE
FUNCTION: 'plpgsql'. Recognized languages are sql, C, internal and the
created procedural languages.
consultas=# select version();
version
-------------------------------------------------------------
PostgreSQL 7.0.3 on i686-pc-linux-gnu, compiled by gcc 2.96
(1 row)
--
Fernando Edurado B. L. e Carvalho
Development/Consultant
^ permalink raw reply [nested|flat] 23+ messages in thread
* plpgsql
@ 2000-12-13 19:27 Jie Liang <jliang@jliang.ipinc.com>
0 siblings, 0 replies; 23+ messages in thread
From: Jie Liang @ 2000-12-13 19:27 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-sql
Hi,
How can I declare an array in plpgsql??
when I use
declare
url text[10];
ERROR: parse error at or near "["
if I use
_text;
declare is OK, however, when I assgin a value after BEGIN
url[i]:=whatever;
get same ERROR,
Is it possible to return an array from a plpgsql function??
Jie LIANG
Internet Products Inc.
10350 Science Center Drive
Suite 100, San Diego, CA 92121
Office:(858)320-4873
jliang@ipinc.com
www.ipinc.com
^ permalink raw reply [nested|flat] 23+ messages in thread
* plpgsql ?
@ 2000-12-20 03:40 guard <guard@ficnet.net>
2000-12-20 20:51 ` Re: plpgsql ? Kovacs Zoltan Sandor <tip@pc10.radnoti-szeged.sulinet.hu>
2000-12-21 00:18 ` Re: plpgsql ? Jie Liang <jliang@jliang.ipinc.com>
0 siblings, 2 replies; 23+ messages in thread
From: guard @ 2000-12-20 03:40 UTC (permalink / raw)
To: pgsql-sql
Hi all,
i have a question 'plpgsql'
my code
CREATE FUNCTION autono (text,text) RETURNS text AS '
DECLARE
a1 ALIAS FOR $1;
a2 ALIAS FOR $1;
ret_val text;
BEGIN
select tna into ret_val from a1 where pamt_no=a2;
RETURN ret_val;
END;' LANGUAGE 'plpgsql'
error run " select tna into ret_val from a1 where pamt_no=a2; "
help me ,thanks
^ permalink raw reply [nested|flat] 23+ messages in thread
* Re: plpgsql ?
2000-12-20 03:40 plpgsql ? guard <guard@ficnet.net>
@ 2000-12-20 20:51 ` Kovacs Zoltan Sandor <tip@pc10.radnoti-szeged.sulinet.hu>
1 sibling, 0 replies; 23+ messages in thread
From: Kovacs Zoltan Sandor @ 2000-12-20 20:51 UTC (permalink / raw)
To: guard <guard@ficnet.net>; +Cc: pgsql-sql
> CREATE FUNCTION autono (text,text) RETURNS text AS '
> DECLARE
> a1 ALIAS FOR $1;
> a2 ALIAS FOR $1;
> ret_val text;
> BEGIN
> select tna into ret_val from a1 where pamt_no=a2;
>
> RETURN ret_val;
> END;' LANGUAGE 'plpgsql'
>
>
> error run " select tna into ret_val from a1 where pamt_no=a2; "
You cannot give a table name as an argument. You should write different
SELECTs for different tables, like this:
if a1 = ''dummy1'' then select tna into ret_val from dummy1 where
pamt_no=a2; end if;
if a1 = ''dummy2'' then select tna into ret_val from dummy2 where
pamt_no=a2; end if;
...
And so on. Unfortunately... :-)
Zoltan
^ permalink raw reply [nested|flat] 23+ messages in thread
* Re: plpgsql ?
2000-12-20 03:40 plpgsql ? guard <guard@ficnet.net>
@ 2000-12-21 00:18 ` Jie Liang <jliang@jliang.ipinc.com>
1 sibling, 0 replies; 23+ messages in thread
From: Jie Liang @ 2000-12-21 00:18 UTC (permalink / raw)
To: guard <guard@ficnet.net>; +Cc: pgsql-sql
Hi, there,
see following.
Jie LIANG
Internet Products Inc.
10350 Science Center Drive
Suite 100, San Diego, CA 92121
Office:(858)320-4873
jliang@ipinc.com
www.ipinc.com
On Wed, 20 Dec 2000, guard wrote:
> Hi all,
>
> i have a question 'plpgsql'
>
> my code
>
> CREATE FUNCTION autono (text,text) RETURNS text AS '
> DECLARE
> a1 ALIAS FOR $1;
> a2 ALIAS FOR $1; <== $2 ??
> ret_val text;
> BEGIN
> select tna into ret_val from a1 where pamt_no=a2;
> -- I think that table name cannot use parameter anyway in plpgsql.
> RETURN ret_val;
> END;' LANGUAGE 'plpgsql'
>
>
> error run " select tna into ret_val from a1 where pamt_no=a2; "
> help me ,thanks
>
>
>
>
>
^ permalink raw reply [nested|flat] 23+ messages in thread
* plpgsql
@ 2001-04-20 23:58 Jie Liang <jliang@ipinc.com>
2001-04-21 01:58 ` Re: plpgsql Roberto Mello <rmello@cc.usu.edu>
0 siblings, 1 reply; 23+ messages in thread
From: Jie Liang @ 2001-04-20 23:58 UTC (permalink / raw)
To: pgsql-sql; pgsql-hacker@postgresql.org
I 've a question about begin...end in plpgsql
does
sql stmts in
begin
....
end;
will go one transaction?
i.e.
begin...end have same meaning as sql stmts BEGIN...COMMIT??
if failed, transaction abort?
if select..for update is used then
another update stmt will wait on the same rows??
if begin...end in plpgsql connot have same functionality as
sql, how can I ensure my sql stmts go one transaction??
thanks.
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
^ permalink raw reply [nested|flat] 23+ messages in thread
* Re: plpgsql
2001-04-20 23:58 plpgsql Jie Liang <jliang@ipinc.com>
@ 2001-04-21 01:58 ` Roberto Mello <rmello@cc.usu.edu>
2001-04-22 21:25 ` Re: plpgsql Jie Liang <jliang@ipinc.com>
0 siblings, 1 reply; 23+ messages in thread
From: Roberto Mello @ 2001-04-21 01:58 UTC (permalink / raw)
To: Jie Liang <jliang@ipinc.com>; +Cc: pgsql-sql; pgsql-hacker@postgresql.org
On Fri, Apr 20, 2001 at 04:58:02PM -0700, Jie Liang wrote:
>
> I 've a question about begin...end in plpgsql
> does
> sql stmts in
> begin
> ....
> end;
> will go one transaction?
Read the documentation (programmer's guide). It's all there. It's to
answer your questions that we take the time to write docs in the first
place :)
Short answer: everything in your function is executed in one
transaction. BEGIN and END in PL/pgSQL are NOT the same as in the
transaction semantics.
-Roberto
--
+----| http://fslc.usu.edu USU Free Software & GNU/Linux Club |------+
Roberto Mello - Computer Science, USU - http://www.brasileiro.net
http://www.sdl.usu.edu - Space Dynamics Lab, Developer
If at first you don't succeed, destroy all evidence that you tried.
^ permalink raw reply [nested|flat] 23+ messages in thread
* Re: plpgsql
2001-04-20 23:58 plpgsql Jie Liang <jliang@ipinc.com>
2001-04-21 01:58 ` Re: plpgsql Roberto Mello <rmello@cc.usu.edu>
@ 2001-04-22 21:25 ` Jie Liang <jliang@ipinc.com>
0 siblings, 0 replies; 23+ messages in thread
From: Jie Liang @ 2001-04-22 21:25 UTC (permalink / raw)
To: Roberto Mello <rmello@cc.usu.edu>; +Cc: pgsql-sql; pgsql-hacker@postgresql.org
Roberto,
Thanks for your help, I read the docs.
I am still not entire sure, my problem is in my plpgsql function
I used
for rec in select ... for update loop
update stmt
end loop;
I don't want above chosen rows be selected(i.e. I want them to be lock
exclusively) by another user until transaction done.
do I need an explicit LOCK stmt?
can it be used in the plpgsql function??
Thanks again.
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 Fri, 20 Apr 2001, Roberto Mello wrote:
> On Fri, Apr 20, 2001 at 04:58:02PM -0700, Jie Liang wrote:
> >
> > I 've a question about begin...end in plpgsql
> > does
> > sql stmts in
> > begin
> > ....
> > end;
> > will go one transaction?
>
> Read the documentation (programmer's guide). It's all there. It's to
> answer your questions that we take the time to write docs in the first
> place :)
> Short answer: everything in your function is executed in one
> transaction. BEGIN and END in PL/pgSQL are NOT the same as in the
> transaction semantics.
>
> -Roberto
> --
> +----| http://fslc.usu.edu USU Free Software & GNU/Linux Club |------+
> Roberto Mello - Computer Science, USU - http://www.brasileiro.net
> http://www.sdl.usu.edu - Space Dynamics Lab, Developer
> If at first you don't succeed, destroy all evidence that you tried.
>
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Don't 'kill -9' the postmaster
>
^ permalink raw reply [nested|flat] 23+ messages in thread
* PLpgSQL
@ 2001-07-20 19:26 Dado Feigenblatt <dado@wildbrain.com>
0 siblings, 0 replies; 23+ messages in thread
From: Dado Feigenblatt @ 2001-07-20 19:26 UTC (permalink / raw)
To: pgsql-sql
3 questions:
1. Can I use CREATE SEQUENCE inside a function?
2. I can create this function but I can't get it to run:
CREATE FUNCTION new_proj_pts_seq(int4)
RETURNS text
AS 'DECLARE
proj_ID alias for $1;
seq_name TEXT;
BEGIN
seq_name := ''proj_pts_'' || proj_ID;
create sequence seq_name;
END;
RETURNS seq_name;'
LANGUAGE 'plpgsql';
When I do
SELECT new_proj_pts_seq(9000);
I get:
ERROR: parser: parse error at or near "$1"
The same happens if I assign $1 to proj_ID (instead of aliasing), or
just use $1 in the string concatenation.
I always get the same message.
Considering that all the above is possible/fixable...
3. On the statement 'create sequence seq_name;', will 'seq_name' be
evaluated properly?
Thanks.
--
Dado Feigenblatt Wild Brain, Inc.
Technical Director (415) 553-8000 x???
dado@wildbrain.com San Francisco, CA.
^ permalink raw reply [nested|flat] 23+ messages in thread
* RE: PLpgSQL
@ 2001-07-20 21:25 Jeff Eckermann <jeckermann@verio.net>
2001-07-20 23:34 ` Re: PLpgSQL Dado Feigenblatt <dado@wildbrain.com>
0 siblings, 1 reply; 23+ messages in thread
From: Jeff Eckermann @ 2001-07-20 21:25 UTC (permalink / raw)
To: 'Dado Feigenblatt' <dado@wildbrain.com>; pgsql-sql
You will need to use "EXECUTE" to create the sequence. The docs on pl/pgsql
cover it: basically, "EXECUTE string" will cause that string to be executed
as a SQL statement.
> -----Original Message-----
> From: Dado Feigenblatt [SMTP:dado@wildbrain.com]
> Sent: Friday, July 20, 2001 2:26 PM
> To: Pgsql-Sql
> Subject: PLpgSQL
>
> 3 questions:
>
> 1. Can I use CREATE SEQUENCE inside a function?
> 2. I can create this function but I can't get it to run:
>
> CREATE FUNCTION new_proj_pts_seq(int4)
> RETURNS text
> AS 'DECLARE
> proj_ID alias for $1;
> seq_name TEXT;
> BEGIN
> seq_name := ''proj_pts_'' || proj_ID;
> create sequence seq_name;
> END;
> RETURNS seq_name;'
> LANGUAGE 'plpgsql';
>
> When I do
> SELECT new_proj_pts_seq(9000);
> I get:
> ERROR: parser: parse error at or near "$1"
>
> The same happens if I assign $1 to proj_ID (instead of aliasing), or
> just use $1 in the string concatenation.
> I always get the same message.
>
> Considering that all the above is possible/fixable...
>
> 3. On the statement 'create sequence seq_name;', will 'seq_name' be
> evaluated properly?
>
> Thanks.
>
>
> --
> Dado Feigenblatt Wild Brain, Inc.
> Technical Director (415) 553-8000 x???
> dado@wildbrain.com San Francisco, CA.
>
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Don't 'kill -9' the postmaster
^ permalink raw reply [nested|flat] 23+ messages in thread
* Re: PLpgSQL
2001-07-20 21:25 RE: PLpgSQL Jeff Eckermann <jeckermann@verio.net>
@ 2001-07-20 23:34 ` Dado Feigenblatt <dado@wildbrain.com>
2001-07-21 16:30 ` Re: Re: PLpgSQL Josh Berkus <josh@agliodbs.com>
0 siblings, 1 reply; 23+ messages in thread
From: Dado Feigenblatt @ 2001-07-20 23:34 UTC (permalink / raw)
To: pgsql-sql
Jeff Eckermann wrote:
>You will need to use "EXECUTE" to create the sequence. The docs on pl/pgsql
>cover it: basically, "EXECUTE string" will cause that string to be executed
>as a SQL statement.
>
Now that I found the documentation, I started to suspect that.
Although nowhere it is mentioned that you can only use INSERT, SELECT,
UPDATE, DELETE.
But still, the $1 is still plaguing me.
Thanks.
>>-----Original Message-----
>>From: Dado Feigenblatt [SMTP:dado@wildbrain.com]
>>Sent: Friday, July 20, 2001 2:26 PM
>>To: Pgsql-Sql
>>Subject: PLpgSQL
>>
>>3 questions:
>>
>>1. Can I use CREATE SEQUENCE inside a function?
>>2. I can create this function but I can't get it to run:
>>
>>CREATE FUNCTION new_proj_pts_seq(int4)
>>RETURNS text
>>AS 'DECLARE
>> proj_ID alias for $1;
>> seq_name TEXT;
>> BEGIN
>> seq_name := ''proj_pts_'' || proj_ID;
>> create sequence seq_name;
>> END;
>> RETURNS seq_name;'
>>LANGUAGE 'plpgsql';
>>
>>When I do
>> SELECT new_proj_pts_seq(9000);
>>I get:
>> ERROR: parser: parse error at or near "$1"
>>
>>The same happens if I assign $1 to proj_ID (instead of aliasing), or
>>just use $1 in the string concatenation.
>>I always get the same message.
>>
>>Considering that all the above is possible/fixable...
>>
>>3. On the statement 'create sequence seq_name;', will 'seq_name' be
>>evaluated properly?
>>
>>Thanks.
>>
>>
>>--
>>Dado Feigenblatt Wild Brain, Inc.
>>Technical Director (415) 553-8000 x???
>>dado@wildbrain.com San Francisco, CA.
>>
>>
>>
>>
>>---------------------------(end of broadcast)---------------------------
>>TIP 4: Don't 'kill -9' the postmaster
>>
>
>
--
Dado Feigenblatt Wild Brain, Inc.
Technical Director (415) 553-8000 x???
dado@wildbrain.com San Francisco, CA.
^ permalink raw reply [nested|flat] 23+ messages in thread
* Re: Re: PLpgSQL
2001-07-20 21:25 RE: PLpgSQL Jeff Eckermann <jeckermann@verio.net>
2001-07-20 23:34 ` Re: PLpgSQL Dado Feigenblatt <dado@wildbrain.com>
@ 2001-07-21 16:30 ` Josh Berkus <josh@agliodbs.com>
2001-07-23 14:27 ` Re: Re: PLpgSQL Jan Wieck <JanWieck@Yahoo.com>
0 siblings, 1 reply; 23+ messages in thread
From: Josh Berkus @ 2001-07-21 16:30 UTC (permalink / raw)
To: Dado Feigenblatt <dado@wildbrain.com>; +Cc: pgsql-sql
Dado,
> But still, the $1 is still plaguing me.
>
Here's your problem:
> >>CREATE FUNCTION new_proj_pts_seq(int4)
> >>RETURNS text
> >>AS 'DECLARE
> >> proj_ID alias for $1;
> >> seq_name TEXT;
> >> BEGIN
> >> seq_name := ''proj_pts_'' || proj_ID;
> >> create sequence seq_name;
> >> END;
> >> RETURNS seq_name;'
This should read: RETURN seq_name
No "S".
> >>LANGUAGE 'plpgsql';
-Josh
______AGLIO DATABASE SOLUTIONS___________________________
Josh Berkus
Complete information technology josh@agliodbs.com
and data management solutions (415) 565-7293
for law firms, small businesses fax 621-2533
and non-profit organizations. San Francisco
^ permalink raw reply [nested|flat] 23+ messages in thread
* Re: Re: PLpgSQL
2001-07-20 21:25 RE: PLpgSQL Jeff Eckermann <jeckermann@verio.net>
2001-07-20 23:34 ` Re: PLpgSQL Dado Feigenblatt <dado@wildbrain.com>
2001-07-21 16:30 ` Re: Re: PLpgSQL Josh Berkus <josh@agliodbs.com>
@ 2001-07-23 14:27 ` Jan Wieck <JanWieck@Yahoo.com>
0 siblings, 0 replies; 23+ messages in thread
From: Jan Wieck @ 2001-07-23 14:27 UTC (permalink / raw)
To: Josh Berkus <josh@agliodbs.com>; +Cc: Dado Feigenblatt <dado@wildbrain.com>; pgsql-sql
Josh Berkus wrote:
> Dado,
>
> > But still, the $1 is still plaguing me.
> >
>
> Here's your problem:
>
> > >>CREATE FUNCTION new_proj_pts_seq(int4)
> > >>RETURNS text
> > >>AS 'DECLARE
> > >> proj_ID alias for $1;
> > >> seq_name TEXT;
> > >> BEGIN
> > >> seq_name := ''proj_pts_'' || proj_ID;
> > >> create sequence seq_name;
> > >> END;
> > >> RETURNS seq_name;'
>
> This should read: RETURN seq_name
>
> No "S".
And should be placed before the END;
>
> > >>LANGUAGE 'plpgsql';
>
> -Josh
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
^ permalink raw reply [nested|flat] 23+ messages in thread
* plpgsql
@ 2003-04-15 17:32 Zodiac <bishop@nm.ru>
2003-04-15 17:37 ` Re: plpgsql mallah@trade-india.com
0 siblings, 1 reply; 23+ messages in thread
From: Zodiac @ 2003-04-15 17:32 UTC (permalink / raw)
To: pgsql-sql
Hello everybody!
I have a problem with using language "plpgsql".
When i try to create plpgsql_call_handler() i receive error message:
"Load of file /usr/local/psql/lib/plpgsql.so failed:
/usr/local/psql/lib/plpgsql.so: undefined symbol: getTypeInputInfo"
Listing of function is:
CREATE FUNCTION plpgsql_call_handler() RETURNS OPAQUE
AS '/usr/local/psql/lib/plpgsql.so'
LANGUAGE 'C';
I'm using PostgreSQL 7.2 installed from rpm's. File plpgsql.so exists and is placed in correct folder.
What this error means and what should i do?
Thanks for any help.
P.S. OS is AspLinux...
^ permalink raw reply [nested|flat] 23+ messages in thread
* Re: plpgsql
2003-04-15 17:32 plpgsql Zodiac <bishop@nm.ru>
@ 2003-04-15 17:37 ` mallah@trade-india.com
2003-04-15 17:52 ` Re: plpgsql Jan Wieck <JanWieck@Yahoo.com>
0 siblings, 1 reply; 23+ messages in thread
From: mallah@trade-india.com @ 2003-04-15 17:37 UTC (permalink / raw)
To: bishop@nm.ru; +Cc: pgsql-sql
Have you tried createlang command?
$ createlang -Uusername plpgsql dbname
> Hello everybody!
> I have a problem with using language "plpgsql".
>
> When i try to create plpgsql_call_handler() i receive error message: "Load of file
> /usr/local/psql/lib/plpgsql.so failed:
> /usr/local/psql/lib/plpgsql.so: undefined symbol: getTypeInputInfo"
>
> Listing of function is:
> CREATE FUNCTION plpgsql_call_handler() RETURNS OPAQUE
> AS '/usr/local/psql/lib/plpgsql.so'
> LANGUAGE 'C';
>
> I'm using PostgreSQL 7.2 installed from rpm's. File plpgsql.so exists and is placed in correct
> folder.
>
> What this error means and what should i do?
>
> Thanks for any help.
>
> P.S. OS is AspLinux...
-----------------------------------------
Get your free web based email at trade-india.com.
"India's Leading B2B eMarketplace.!"
http://www.trade-india.com/
^ permalink raw reply [nested|flat] 23+ messages in thread
* Re: plpgsql
2003-04-15 17:32 plpgsql Zodiac <bishop@nm.ru>
2003-04-15 17:37 ` Re: plpgsql mallah@trade-india.com
@ 2003-04-15 17:52 ` Jan Wieck <JanWieck@Yahoo.com>
2003-04-16 05:26 ` Re: plpgsql Rajesh Kumar Mallah <mallah@trade-india.com>
0 siblings, 1 reply; 23+ messages in thread
From: Jan Wieck @ 2003-04-15 17:52 UTC (permalink / raw)
To: mallah@trade-india.com; +Cc: bishop@nm.ru; pgsql-sql
mallah@trade-india.com wrote:
>
> Have you tried createlang command?
I doubt that would work. Looks to me like a version mismatch between the
backend and the loadable object for the PL. Who installed that system
and was there lately some upgrade attempt?
Jan
>
> $ createlang -Uusername plpgsql dbname
>
> > Hello everybody!
> > I have a problem with using language "plpgsql".
> >
> > When i try to create plpgsql_call_handler() i receive error message: "Load of file
> > /usr/local/psql/lib/plpgsql.so failed:
> > /usr/local/psql/lib/plpgsql.so: undefined symbol: getTypeInputInfo"
> >
> > Listing of function is:
> > CREATE FUNCTION plpgsql_call_handler() RETURNS OPAQUE
> > AS '/usr/local/psql/lib/plpgsql.so'
> > LANGUAGE 'C';
> >
> > I'm using PostgreSQL 7.2 installed from rpm's. File plpgsql.so exists and is placed in correct
> > folder.
> >
> > What this error means and what should i do?
> >
> > Thanks for any help.
> >
> > P.S. OS is AspLinux...
>
> -----------------------------------------
> Get your free web based email at trade-india.com.
> "India's Leading B2B eMarketplace.!"
> http://www.trade-india.com/
>
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Don't 'kill -9' the postmaster
--
#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me. #
#================================================== JanWieck@Yahoo.com #
^ permalink raw reply [nested|flat] 23+ messages in thread
* Re: plpgsql
2003-04-15 17:32 plpgsql Zodiac <bishop@nm.ru>
2003-04-15 17:37 ` Re: plpgsql mallah@trade-india.com
2003-04-15 17:52 ` Re: plpgsql Jan Wieck <JanWieck@Yahoo.com>
@ 2003-04-16 05:26 ` Rajesh Kumar Mallah <mallah@trade-india.com>
0 siblings, 0 replies; 23+ messages in thread
From: Rajesh Kumar Mallah @ 2003-04-16 05:26 UTC (permalink / raw)
To: Jan Wieck <JanWieck@Yahoo.com>; +Cc: bishop@nm.ru; pgsql-sql
yes it looks like from the undefined symbol error. but
ironically createlang worked for him.
>Thank you!!!
>Everything is OK!!
>I just tried only with CREATE FUNCTON and forgot about CREATELANG.
>Thanx!
On Tuesday 15 Apr 2003 11:22 pm, Jan Wieck wrote:
> mallah@trade-india.com wrote:
> > Have you tried createlang command?
>
> I doubt that would work. Looks to me like a version mismatch between the
> backend and the loadable object for the PL. Who installed that system
> and was there lately some upgrade attempt?
>
>
> Jan
>
> > $ createlang -Uusername plpgsql dbname
> >
> > > Hello everybody!
> > > I have a problem with using language "plpgsql".
> > >
> > > When i try to create plpgsql_call_handler() i receive error message:
> > > "Load of file /usr/local/psql/lib/plpgsql.so failed:
> > > /usr/local/psql/lib/plpgsql.so: undefined symbol: getTypeInputInfo"
> > >
> > > Listing of function is:
> > > CREATE FUNCTION plpgsql_call_handler() RETURNS OPAQUE
> > > AS '/usr/local/psql/lib/plpgsql.so'
> > > LANGUAGE 'C';
> > >
> > > I'm using PostgreSQL 7.2 installed from rpm's. File plpgsql.so exists
> > > and is placed in correct folder.
> > >
> > > What this error means and what should i do?
> > >
> > > Thanks for any help.
> > >
> > > P.S. OS is AspLinux...
> >
> > -----------------------------------------
> > Get your free web based email at trade-india.com.
> > "India's Leading B2B eMarketplace.!"
> > http://www.trade-india.com/
> >
> > ---------------------------(end of broadcast)---------------------------
> > TIP 4: Don't 'kill -9' the postmaster
--
Rajesh Kumar Mallah,
Project Manager (Development)
Infocom Network Limited, New Delhi
phone: +91(11)6152172 (221) (L) ,9811255597 (M)
Visit http://www.trade-india.com ,
India's Leading B2B eMarketplace.
^ permalink raw reply [nested|flat] 23+ messages in thread
end of thread, other threads:[~2003-04-16 05:26 UTC | newest]
Thread overview: 23+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2000-12-08 20:51 plpgsql Jie Liang <jliang@ipinc.com>
2000-12-12 06:42 ` Kovacs Zoltan Sandor <tip@pc10.radnoti-szeged.sulinet.hu>
2000-12-12 18:14 ` Jie Liang <jliang@jliang.ipinc.com>
2000-12-09 08:16 plpgsql feblec <carvalho@ig.com.br>
2000-12-12 16:20 ` Oliver Elphick <olly@lfix.co.uk>
2000-12-12 16:56 ` Mike Castle <dalgoda@ix.netcom.com>
2000-12-12 13:30 Plpgsql feblec <carvalho@ig.com.br>
2000-12-13 19:27 plpgsql Jie Liang <jliang@jliang.ipinc.com>
2000-12-20 03:40 plpgsql ? guard <guard@ficnet.net>
2000-12-20 20:51 ` Re: plpgsql ? Kovacs Zoltan Sandor <tip@pc10.radnoti-szeged.sulinet.hu>
2000-12-21 00:18 ` Re: plpgsql ? Jie Liang <jliang@jliang.ipinc.com>
2001-04-20 23:58 plpgsql Jie Liang <jliang@ipinc.com>
2001-04-21 01:58 ` Roberto Mello <rmello@cc.usu.edu>
2001-04-22 21:25 ` Jie Liang <jliang@ipinc.com>
2001-07-20 19:26 PLpgSQL Dado Feigenblatt <dado@wildbrain.com>
2001-07-20 21:25 RE: PLpgSQL Jeff Eckermann <jeckermann@verio.net>
2001-07-20 23:34 ` Re: PLpgSQL Dado Feigenblatt <dado@wildbrain.com>
2001-07-21 16:30 ` Re: Re: PLpgSQL Josh Berkus <josh@agliodbs.com>
2001-07-23 14:27 ` Re: Re: PLpgSQL Jan Wieck <JanWieck@Yahoo.com>
2003-04-15 17:32 plpgsql Zodiac <bishop@nm.ru>
2003-04-15 17:37 ` mallah@trade-india.com
2003-04-15 17:52 ` Jan Wieck <JanWieck@Yahoo.com>
2003-04-16 05:26 ` Rajesh Kumar Mallah <mallah@trade-india.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox