pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedPL/pgSQL
13+ messages / 12 participants
[nested] [flat]
* PL/pgSQL
@ 2000-05-27 20:03 Antony Sakellariou <vansak@otenet.gr>
0 siblings, 0 replies; 13+ messages in thread
From: Antony Sakellariou @ 2000-05-27 20:03 UTC (permalink / raw)
To: pgsql-sql
I m using postgresql under linux suse 6.3. I have installed the language as the documentation indicates by creating the call handler and the language.
Trying to create a function within a database the psql interface is replying that the function is created.
But when I call the function ( it returns just an integer) I see the message " stat failed on file '/usr/lib/pgsql/lib/plpgsql.so' no such file" . The strange is that the file exists.
Please give me an explanation
Regards
Anthony Sakellariou
Greece
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: PL/pgSQL
@ 2000-08-03 15:33 Mark Volpe <volpe.mark@epamail.epa.gov>
0 siblings, 0 replies; 13+ messages in thread
From: Mark Volpe @ 2000-08-03 15:33 UTC (permalink / raw)
To: pgsql-sql
Oh yeah, I'm using v7.0
Mark
^ permalink raw reply [nested|flat] 13+ messages in thread
* PL/pgSQL
@ 2000-08-07 20:00 Jie Liang <jliang@ipinc.com>
0 siblings, 0 replies; 13+ messages in thread
From: Jie Liang @ 2000-08-07 20:00 UTC (permalink / raw)
To: pgsql-sql
Hi, there,
1.
Is anybody know how to declare a explicit cursor in PL/SQL in postgres?
1. I want create a function use PL/pgSQL:
create function foo() returns whatevertype as '
declare
v_asda int2;
cursor is select a,b,c from AAA; -- what is the syntex?
begin
statements
end;
' langauge 'plpgsql';
2. for the record type, can I explicitly define my own record like:
declare
type blah is record(ename text,job text,id int 4);
type blahblah is table of blah index by int4; -- like in
Oracle
......
3. how to define an array in PL/pgSQL declare block and assign it in
execuable block?
like:
declare
ary int[5]; -- in PL/pgSQL maybe is _int;
began
ary[1]:= 3; -- give me error when I run it;
end;
Thanks.
--
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] 13+ messages in thread
* PL/pgSQL
@ 2000-08-15 17:06 Jie Liang <jliang@ipinc.com>
0 siblings, 0 replies; 13+ messages in thread
From: Jie Liang @ 2000-08-15 17:06 UTC (permalink / raw)
To: pgsql-sql
Hi,
Is anybody know how to declare a explicit cursor in PL/pgSQL (not in
SQL) ???
syntax:
declare
cursor mycur is select statement ?? it won't work!
--
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] 13+ messages in thread
* PL/PGSQL
@ 2001-06-05 06:18 Andrzej Roszkowski <thomas@polcard.com.pl>
2001-06-12 17:15 ` Re: PL/PGSQL Alex Pilosov <alex@pilosoft.com>
2001-06-12 19:48 ` Re: PL/PGSQL Roberto Mello <rmello@cc.usu.edu>
0 siblings, 2 replies; 13+ messages in thread
From: Andrzej Roszkowski @ 2001-06-05 06:18 UTC (permalink / raw)
To: pgsql-sql
Hi!
I can't find any info about possible data types that can be returned from
procedure. I want to return tuple. When I use OPAQUE as a return type I
get error message that OPAQUE can be only returned from trigger. It is
possible to do this without triggers?
------------------------------------------------------------------------
Code reviews are like sex, just anyone can do it, but skill and training
can make you a lot better at it." - LJ
Thomas
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: PL/PGSQL
2001-06-05 06:18 PL/PGSQL Andrzej Roszkowski <thomas@polcard.com.pl>
@ 2001-06-12 17:15 ` Alex Pilosov <alex@pilosoft.com>
1 sibling, 0 replies; 13+ messages in thread
From: Alex Pilosov @ 2001-06-12 17:15 UTC (permalink / raw)
To: Andrzej Roszkowski <thomas@polcard.com.pl>; +Cc: pgsql-sql
You _can_ return a tuple by doing this:
create table foo(x int, y text);
create function bar() returns foo as '
...
';
The only thing is, you can't do much with such a function (currently), you
can only call it from a different function, and then break the result up:
foo v=bar();
do_something(v.x);
do_something(v.y);
-alex
On Tue, 5 Jun 2001, Andrzej Roszkowski wrote:
> Hi!
> I can't find any info about possible data types that can be returned from
> procedure. I want to return tuple. When I use OPAQUE as a return type I
> get error message that OPAQUE can be only returned from trigger. It is
> possible to do this without triggers?
>
> ------------------------------------------------------------------------
> Code reviews are like sex, just anyone can do it, but skill and training
> can make you a lot better at it." - LJ
> Thomas
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 5: Have you checked our extensive FAQ?
>
> http://www.postgresql.org/users-lounge/docs/faq.html
>
>
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: PL/PGSQL
2001-06-05 06:18 PL/PGSQL Andrzej Roszkowski <thomas@polcard.com.pl>
@ 2001-06-12 19:48 ` Roberto Mello <rmello@cc.usu.edu>
1 sibling, 0 replies; 13+ messages in thread
From: Roberto Mello @ 2001-06-12 19:48 UTC (permalink / raw)
To: Andrzej Roszkowski <thomas@polcard.com.pl>; +Cc: pgsql-sql
On Tue, Jun 05, 2001 at 08:18:03AM +0200, Andrzej Roszkowski wrote:
> Hi!
> I can't find any info about possible data types that can be returned from
> procedure. I want to return tuple. When I use OPAQUE as a return type I
> get error message that OPAQUE can be only returned from trigger. It is
> possible to do this without triggers?
A little RTFM'ing wouldn't hurt.
Currently you can't return tuples from "regular" PL/pgSQL functions.
It heard this functionality will make it in 7.2.
-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
Death to all fanatics!!!
^ permalink raw reply [nested|flat] 13+ messages in thread
* PL/pgsql
@ 2002-03-04 18:32 Ismail Bouabdallah <bouabdi9@cs.man.ac.uk>
2002-03-08 19:54 ` Re: PL/pgsql PG Explorer <pgmail@pgexplorer.com>
0 siblings, 1 reply; 13+ messages in thread
From: Ismail Bouabdallah @ 2002-03-04 18:32 UTC (permalink / raw)
To: pgsql-sql
Hi can any one help me out with the following trigger that I have
written....
CREATE FUNCTION new_site() RETURNS OPAQUE AS '
BEGIN
IF NEW.SITE_REFERNCE = SITE_REFERENCE
THEN
SITE.END_DATE := NEW.START_DATE
SITE.CURRENCY_FLAG := 2
END;
' LANGUAGE 'plpgsql';
CREATE TRIGGER NEWSITE
AFTER INSERT ON SITE
FOR EACH ROW
EXECUTE PROCEDURE new_site();
the trigger enforces versioning of new sites inserted into the site
table of my land_use database....
but after defining the function I can no longer insert new sites into
the database.
i receive the following error from psql:
ERROR: fmgr_info: function 56664: cache lookup failed
when I try to insert the following data:
INSERT INTO SITE
(SITE_REFERENCE,UPRN,LA_REFERENCE,VERSION_NO,CURRENCY_FLAG,START_DATE,END_DATE,EASTING,NORTHING,PAO_DESCRIPTION,STREET_DESCRIPTION,LOCALITY_DESCRIPTION,TOWN_DESCRIPTION,POSTCODE,LA_CODE,AREA,LAND_TYPE_REF,LAND_USE_DIVISION_REF,LAND_USE_CLASS_REF,LAND_USE_SUB_CLASS_REF,DERELICTION_REF,OWNER_REF,PLANNING_STATUS_REF,PROPOSED_USE_REF,HOUSING_COMPONENT,HOUSING_SUIT_REF,MOST_SUITABLE_USE_REF,EST_HOUSING_CAPACITY,HOUSING_DENSITY,LEGAL_CONSTRAINTS,PHYSICAL_CONSTRAINTS,AVAILABLE_ON_MARKET,AGENT_NAME,EP_RDA_INVOLVEMENT,GENERAL_SITE_INFORMATION,BOUNDARY_ID)
VALUES (345600001,null,null,2,1,'20020311',null,414722,130160,'57 TO
61','GREENCROFT STREET',null,'SNAILSBURY','SN3
5BX',3456,0.062,2,11,44,133,11,3,4,2,4,1,2,10,161,1,2,3,null,2,null,3456000011);
Attachments:
bouabdi9.vcf (text/x-vcard, 187B) download
begin:vcard
n:Bouabdallah;Ismail
x-mozilla-html:FALSE
adr:;;;;;;
version:2.1
email;internet:bouabdi9@cs.man.ac.uk`
title:Mr I.Bouabdallah
x-mozilla-cpt:;0
fn:Ismail Bouabdallah
end:vcard
* Re: PL/pgsql
2002-03-04 18:32 PL/pgsql Ismail Bouabdallah <bouabdi9@cs.man.ac.uk>
@ 2002-03-08 19:54 ` PG Explorer <pgmail@pgexplorer.com>
2002-03-11 10:39 ` Re: PL/pgsql adm <adm@yantel.ru>
0 siblings, 1 reply; 13+ messages in thread
From: PG Explorer @ 2002-03-08 19:54 UTC (permalink / raw)
To: Ismail Bouabdallah <bouabdi9@cs.man.ac.uk>; pgsql-sql
You have a syntax typo
IF NEW.SITE_REFERNCE = SITE_REFERENCE
IF NEW.SITE_REFERENCE = SITE_REFERENCE
http://www.pgexplorer.com
PostgreSQL GUI Tool
----- Original Message -----
From: "Ismail Bouabdallah" <bouabdi9@cs.man.ac.uk>
To: <pgsql-sql@postgresql.org>
Sent: Monday, March 04, 2002 8:32 PM
Subject: [SQL] PL/pgsql
> Hi can any one help me out with the following trigger that I have
> written....
>
>
> CREATE FUNCTION new_site() RETURNS OPAQUE AS '
> BEGIN
> IF NEW.SITE_REFERNCE = SITE_REFERENCE
> THEN
> SITE.END_DATE := NEW.START_DATE
> SITE.CURRENCY_FLAG := 2
> END;
> ' LANGUAGE 'plpgsql';
>
> CREATE TRIGGER NEWSITE
> AFTER INSERT ON SITE
> FOR EACH ROW
> EXECUTE PROCEDURE new_site();
>
> the trigger enforces versioning of new sites inserted into the site
> table of my land_use database....
> but after defining the function I can no longer insert new sites into
> the database.
>
> i receive the following error from psql:
>
> ERROR: fmgr_info: function 56664: cache lookup failed
>
> when I try to insert the following data:
>
> INSERT INTO SITE
>
(SITE_REFERENCE,UPRN,LA_REFERENCE,VERSION_NO,CURRENCY_FLAG,START_DATE,END_DA
TE,EASTING,NORTHING,PAO_DESCRIPTION,STREET_DESCRIPTION,LOCALITY_DESCRIPTION,
TOWN_DESCRIPTION,POSTCODE,LA_CODE,AREA,LAND_TYPE_REF,LAND_USE_DIVISION_REF,L
AND_USE_CLASS_REF,LAND_USE_SUB_CLASS_REF,DERELICTION_REF,OWNER_REF,PLANNING_
STATUS_REF,PROPOSED_USE_REF,HOUSING_COMPONENT,HOUSING_SUIT_REF,MOST_SUITABLE
_USE_REF,EST_HOUSING_CAPACITY,HOUSING_DENSITY,LEGAL_CONSTRAINTS,PHYSICAL_CON
STRAINTS,AVAILABLE_ON_MARKET,AGENT_NAME,EP_RDA_INVOLVEMENT,GENERAL_SITE_INFO
RMATION,BOUNDARY_ID)
>
> VALUES (345600001,null,null,2,1,'20020311',null,414722,130160,'57 TO
> 61','GREENCROFT STREET',null,'SNAILSBURY','SN3
>
5BX',3456,0.062,2,11,44,133,11,3,4,2,4,1,2,10,161,1,2,3,null,2,null,34560000
11);
>
>
>
----------------------------------------------------------------------------
----
>
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Don't 'kill -9' the postmaster
>
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: PL/pgsql
2002-03-04 18:32 PL/pgsql Ismail Bouabdallah <bouabdi9@cs.man.ac.uk>
2002-03-08 19:54 ` Re: PL/pgsql PG Explorer <pgmail@pgexplorer.com>
@ 2002-03-11 10:39 ` adm <adm@yantel.ru>
0 siblings, 0 replies; 13+ messages in thread
From: adm @ 2002-03-11 10:39 UTC (permalink / raw)
To: pgsql-sql
> You have a syntax typo
> IF NEW.SITE_REFERNCE = SITE_REFERENCE
>
> IF NEW.SITE_REFERENCE = SITE_REFERENCE
PROBABLY
> ----- Original Message -----
> From: "Ismail Bouabdallah" <bouabdi9@cs.man.ac.uk>
> To: <pgsql-sql@postgresql.org>
> Sent: Monday, March 04, 2002 8:32 PM
> Subject: [SQL] PL/pgsql
>
> > ERROR: fmgr_info: function 56664: cache lookup failed
> >
But this message I've got when I've not deleted trigger on table. Try to
delete and create trigger function and trigger again.
GOOD LUCK!
Dinar
^ permalink raw reply [nested|flat] 13+ messages in thread
* PL/pgsql
@ 2002-09-25 19:38 Gustavo Tadao Okida <Gtadaodev@netscape.net>
2002-09-27 20:16 ` Re: PL/pgsql Ian Barwick <barwick@gmx.net>
0 siblings, 1 reply; 13+ messages in thread
From: Gustavo Tadao Okida @ 2002-09-25 19:38 UTC (permalink / raw)
To: pgsql-sql
Hi,
I'm a new PostgreSql user but I have some experience in Oracle. So I
decided to use PL/pgsql to build my procedure (functions).
I create an database called MyDatabase and add this language into it
with: createlang plpgsql MyDatabase. After this, I wrote a function with
pgaccess :
Name: spi_novo_parametro paramters:
returns: char language: plpgsql
DECLARE
val INTEGER :=1;
BEGIN
insert into identificador_pri values(1, "OK");
RETURN "OK";
END;
It was OK when I saved it in pgaccess front end.
When I tested it, by query tab, writing select spi_novo_parametro();, it
return me this message:
Error: can't read
"PgAcVar(mw,.pgaw:22,nrecs)": no such element in array
and the stack is:
Error: can't read "PgAcVar(mw,$wn,nrecs)": no such element in array
while executing
"For {set row 0} {$row<$PgAcVar(mw,$wn,nrecs)} {incr row}{
if {[lindex $PgAcVar(mw,$wn,nrecs) $row]>$y} break }"
(procedure "Tables::canvasClick" line 5)
invoked from within
"Tables::canvasClick .pgaw:22 229 240"
(command bound to event)
Could you help me?
Thanks in advance.
Tadao
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: PL/pgsql
2002-09-25 19:38 PL/pgsql Gustavo Tadao Okida <Gtadaodev@netscape.net>
@ 2002-09-27 20:16 ` Ian Barwick <barwick@gmx.net>
0 siblings, 0 replies; 13+ messages in thread
From: Ian Barwick @ 2002-09-27 20:16 UTC (permalink / raw)
To: Gustavo Tadao Okida <Gtadaodev@netscape.net>; pgsql-sql
On Wednesday 25 September 2002 21:38, Gustavo Tadao Okida wrote:
(...)
> After this, I wrote a function with
> pgaccess :
>
> Name: spi_novo_parametro paramters:
> returns: char language: plpgsql
>
> DECLARE
> val INTEGER :=1;
> BEGIN
> insert into identificador_pri values(1, "OK");
> RETURN "OK";
> END;
>
> It was OK when I saved it in pgaccess front end.
>
> When I tested it, by query tab, writing select spi_novo_parametro();, it
> return me this message:
>
> Error: can't read
> "PgAcVar(mw,.pgaw:22,nrecs)": no such element in array
(...)
I don't know PgAccess, but possibly your function is incorrect
and the errors produced on execution are confusing PgAccess?
Try defining and executing the function in the psql command line client.
This may give you a better idea of what is happening.
I would also strongly recommend replacing the double quotes
with pairs of single quotes, e.g. ''OK'' instead of "OK" .
Ian Barwick
barwick@gmx.net
^ permalink raw reply [nested|flat] 13+ messages in thread
* PL/pgSQL
@ 2003-03-01 22:57 Zodiac <bishop@nm.ru>
0 siblings, 0 replies; 13+ messages in thread
From: Zodiac @ 2003-03-01 22:57 UTC (permalink / raw)
To: pgsql-sql
Hello!
I have a problem with functions on PL/pgSQL.
When i created "plpgsql_call_handler()" and turned on PL/pgSQL support by CREATE LANGUAGE i tried to create a function.
But i got error message that language does not exists, although there is an error "language already exists" if i want to create it again.
Could anybody help me?
Thanks for any help.
^ permalink raw reply [nested|flat] 13+ messages in thread
end of thread, other threads:[~2003-03-01 22:57 UTC | newest]
Thread overview: 13+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2000-05-27 20:03 PL/pgSQL Antony Sakellariou <vansak@otenet.gr>
2000-08-03 15:33 Re: PL/pgSQL Mark Volpe <volpe.mark@epamail.epa.gov>
2000-08-07 20:00 PL/pgSQL Jie Liang <jliang@ipinc.com>
2000-08-15 17:06 PL/pgSQL Jie Liang <jliang@ipinc.com>
2001-06-05 06:18 PL/PGSQL Andrzej Roszkowski <thomas@polcard.com.pl>
2001-06-12 17:15 ` Re: PL/PGSQL Alex Pilosov <alex@pilosoft.com>
2001-06-12 19:48 ` Re: PL/PGSQL Roberto Mello <rmello@cc.usu.edu>
2002-03-04 18:32 PL/pgsql Ismail Bouabdallah <bouabdi9@cs.man.ac.uk>
2002-03-08 19:54 ` Re: PL/pgsql PG Explorer <pgmail@pgexplorer.com>
2002-03-11 10:39 ` Re: PL/pgsql adm <adm@yantel.ru>
2002-09-25 19:38 PL/pgsql Gustavo Tadao Okida <Gtadaodev@netscape.net>
2002-09-27 20:16 ` Re: PL/pgsql Ian Barwick <barwick@gmx.net>
2003-03-01 22:57 PL/pgSQL Zodiac <bishop@nm.ru>
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