pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedPL/pgSQL
8+ messages / 7 participants
[nested] [flat]
* PL/pgSQL
@ 2001-03-02 15:55 Oliver Vecernik <vecernik@aon.at>
2001-03-02 16:57 ` Re: PL/pgSQL Stephan Szabo <sszabo@megazone23.bigpanda.com>
0 siblings, 1 reply; 8+ messages in thread
From: Oliver Vecernik @ 2001-03-02 15:55 UTC (permalink / raw)
To: pgsql-admin
Hi,
I recently started using postgresql. I'm running version 7.0.3 on a SuSE
7.1 Linux box. I'm trying to test the examples in the user's guide. But
I can not create a function with language 'plpgsql'.
It seems there has some installation be done to achieve that. But how?
Can anybody point me to the doc on this or give me some other hints?
Oliver
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: PL/pgSQL
2001-03-02 15:55 PL/pgSQL Oliver Vecernik <vecernik@aon.at>
@ 2001-03-02 16:57 ` Stephan Szabo <sszabo@megazone23.bigpanda.com>
2001-03-02 19:01 ` Re: PL/pgSQL Richard Bayet <bayet@enseirb.fr>
0 siblings, 1 reply; 8+ messages in thread
From: Stephan Szabo @ 2001-03-02 16:57 UTC (permalink / raw)
To: Oliver Vecernik <vecernik@aon.at>; +Cc: pgsql-admin
You need to run createlang to install the procedural languages
(createlang <language> <db>).
On Fri, 2 Mar 2001, Oliver Vecernik wrote:
> Hi,
>
> I recently started using postgresql. I'm running version 7.0.3 on a SuSE
> 7.1 Linux box. I'm trying to test the examples in the user's guide. But
> I can not create a function with language 'plpgsql'.
>
> It seems there has some installation be done to achieve that. But how?
> Can anybody point me to the doc on this or give me some other hints?
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: PL/pgSQL
2001-03-02 15:55 PL/pgSQL Oliver Vecernik <vecernik@aon.at>
2001-03-02 16:57 ` Re: PL/pgSQL Stephan Szabo <sszabo@megazone23.bigpanda.com>
@ 2001-03-02 19:01 ` Richard Bayet <bayet@enseirb.fr>
0 siblings, 0 replies; 8+ messages in thread
From: Richard Bayet @ 2001-03-02 19:01 UTC (permalink / raw)
To: Oliver Vecernik <vecernik@aon.at>; +Cc: pgsql-admin
First, verify that the environment variable PGLIB is set to the directory
where plpgsql.so can be found.
Then use createlang as mentionned: createlang plpgsql dbname.
Note that you can make the plpgsql language available for all database if
dbname is "template1" (the "Queen" of all databases)...
PS: sorry Stephan for the private mail :)
Stephan Szabo a écrit :
> You need to run createlang to install the procedural languages
> (createlang <language> <db>).
>
> On Fri, 2 Mar 2001, Oliver Vecernik wrote:
>
> > Hi,
> >
> > I recently started using postgresql. I'm running version 7.0.3 on a SuSE
> > 7.1 Linux box. I'm trying to test the examples in the user's guide. But
> > I can not create a function with language 'plpgsql'.
> >
> > It seems there has some installation be done to achieve that. But how?
> > Can anybody point me to the doc on this or give me some other hints?
>
> ---------------------------(end of broadcast)---------------------------
> TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org
^ permalink raw reply [nested|flat] 8+ messages in thread
* PL/pgSQL
@ 2002-07-26 13:52 Heni Lolov <hal_bg@yahoo.com>
0 siblings, 0 replies; 8+ messages in thread
From: Heni Lolov @ 2002-07-26 13:52 UTC (permalink / raw)
To: pgsql-admin
Hi there
I have a problem that I can not solve.
So is there any way to return a couple of rows from a PL/pgSQL function. I need
this because I have to execute a query with undefined depth of subqueryes (not
more than 10). The searched table represents a tree. And I have to find all sub
branches og a given branch.
here is the table:
CREATE SEQUENCE grp_id_seq;
CREATE TABLE grp (
id INT PRIMARY KEY NOT NULL DEFAULT nextval('grp_id_seq'),
name VARCHAR NOT NULL,
info VARCHAR,
type_id INT DEFAULT NULL REFERENCES grp_types(id),
mastergrp_id INT REFERENCES grp(id)
);
I need it to use it in a query like this:
SELECT foo FROM footable WHERE groups IN sub_grp(some_grp_id);
Any idea how to make it?
Thanks in advance!
Hal
__________________________________________________
Do You Yahoo!?
Yahoo! Health - Feel better, live better
http://health.yahoo.com
^ permalink raw reply [nested|flat] 8+ messages in thread
* PL/pgsql
@ 2004-06-29 20:52 Tuttle, Gene <cetuttle@rottlundhomes.com>
0 siblings, 0 replies; 8+ messages in thread
From: Tuttle, Gene @ 2004-06-29 20:52 UTC (permalink / raw)
To: pgsql-admin
I am new to Postgresql and am having problems getting functions working.
I have been following an example in the book "PostgreSQL" by Douglas &
Douglas Published by Developers Library.
I put the code in as a function (see end of email)
When I execute it from psql I get the following:
dev=# select my_factorial(1);
server closed the connection unexpectedly
This probably means the server terminated abnormally
before or while processing the request.
The connection to the server was lost. Attempting reset: Failed.
Can any one help me find the problem. point me in the right direction?
Thanks
Gene Tuttle
-- Function: public.my_factorial(int4)
-- DROP FUNCTION public.my_factorial(int4);
CREATE OR REPLACE FUNCTION public.my_factorial(int4)
RETURNS int4 AS
'
DECLARE
arg INTEGER;
BEGIN
arg := $1;
IF arg IS NULL or arg < 0 THEN
RAISE NOTICE \'Invalid Number\';
RETURN NULL;
ELSE
IF arg = 1 THEN
RETURN 1;
ELSE
DECLARE
next_value INTEGER;
BEGIN
next_value := my_factorial(arg - 1) * arg;
RETURN next_value;
END;
END IF;
END IF;
END;
'
LANGUAGE 'plpgsql' VOLATILE;
^ permalink raw reply [nested|flat] 8+ messages in thread
* PL/pgSQL
@ 2004-06-30 13:58 Tuttle, Gene <cetuttle@rottlundhomes.com>
2004-06-30 16:03 ` Re: PL/pgSQL Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 8+ messages in thread
From: Tuttle, Gene @ 2004-06-30 13:58 UTC (permalink / raw)
To: pgsql-admin
I am not able to run any user created functions.
Thanks for your help. Gene Tuttle.
Postgres 7.4
Redhat 9
Debug output:
DEBUG: StartTransactionCommand
LOG: statement: select my_factorial(1);
DEBUG: parse tree:
DETAIL: {QUERY :commandType 1 :querySource 0 :canSetTag true :utilityStmt
<>
:resultRelation 0 :into <> :hasAggs false :hasSubLinks false :rtable
<>
:jointree {FROMEXPR :fromlist <> :quals <>} :rowMarks () :targetList
({TARGETENTRY :resdom {RESDOM :resno 1 :restype 23 :restypmod -1
:resname
my_factorial :ressortgroupref 0 :resorigtbl 0 :resorigcol 0 :resjunk
false}
:expr {FUNCEXPR :funcid 34208 :funcresulttype 23 :funcretset false
:funcformat
0 :args ({CONST :consttype 23 :constlen 4 :constbyval true
:constisnull false
:constvalue 4 [ 1 0 0 0 ]})}}) :groupClause <> :havingQual <>
:distinctClause
<> :sortClause <> :limitOffset <> :limitCount <> :setOperations <>
:resultRelations ()}
DEBUG: rewritten parse tree:
DETAIL: ({QUERY :commandType 1 :querySource 0 :canSetTag true :utilityStmt
<>
:resultRelation 0 :into <> :hasAggs false :hasSubLinks false :rtable
<>
:jointree {FROMEXPR :fromlist <> :quals <>} :rowMarks () :targetList
({TARGETENTRY :resdom {RESDOM :resno 1 :restype 23 :restypmod -1
:resname
my_factorial :ressortgroupref 0 :resorigtbl 0 :resorigcol 0 :resjunk
false}
:expr {FUNCEXPR :funcid 34208 :funcresulttype 23 :funcretset false
:funcformat
0 :args ({CONST :consttype 23 :constlen 4 :constbyval true
:constisnull false
:constvalue 4 [ 1 0 0 0 ]})}}) :groupClause <> :havingQual <>
:distinctClause
<> :sortClause <> :limitOffset <> :limitCount <> :setOperations <>
:resultRelations ()})
DEBUG: plan:
DETAIL: {RESULT :startup_cost 0.00 :total_cost 0.01 :plan_rows 1
:plan_width 0
:targetlist ({TARGETENTRY :resdom {RESDOM :resno 1 :restype 23
:restypmod -1
:resname my_factorial :ressortgroupref 0 :resorigtbl 0 :resorigcol 0
:resjunk
false} :expr {FUNCEXPR :funcid 34208 :funcresulttype 23 :funcretset
false
:funcformat 0 :args ({CONST :consttype 23 :constlen 4 :constbyval
true
:constisnull false :constvalue 4 [ 1 0 0 0 ]})}}) :qual <> :lefttree
<>
:righttree <> :initPlan <> :extParam () :allParam () :nParamExec 0
:resconstantqual <>}
DEBUG: PortalRun
DEBUG: reaping dead processes
DEBUG: child process (PID 28036) was terminated by signal 11
LOG: server process (PID 28036) was terminated by signal 11
LOG: terminating any other active server processes
LOG: all server processes terminated; reinitializing
DEBUG: shmem_exit(0)
DEBUG: invoking IpcMemoryCreate(size=10436608)
LOG: database system was interrupted at 2004-06-30 08:33:40 CDT
LOG: checkpoint record is at 0/1534858
LOG: redo record is at 0/1534858; undo record is at 0/0; shutdown TRUE
LOG: next transaction ID: 16669; next OID: 42400
LOG: database system was not properly shut down; automatic recovery in
progress
FATAL: the database system is starting up
DEBUG: proc_exit(0)
DEBUG: shmem_exit(0)
DEBUG: exit(0)
DEBUG: forked new backend, pid=28039 socket=8
DEBUG: reaping dead processes
DEBUG: child process (PID 28039) exited with exit code 0
LOG: record with zero length at 0/1534898
LOG: redo is not required
LOG: database system is ready
DEBUG: proc_exit(0)
DEBUG: shmem_exit(0)
DEBUG: exit(0)
DEBUG: reaping dead processes
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: PL/pgSQL
2004-06-30 13:58 PL/pgSQL Tuttle, Gene <cetuttle@rottlundhomes.com>
@ 2004-06-30 16:03 ` Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 0 replies; 8+ messages in thread
From: Tom Lane @ 2004-06-30 16:03 UTC (permalink / raw)
To: Tuttle, Gene <cetuttle@rottlundhomes.com>; +Cc: pgsql-admin
"Tuttle, Gene" <cetuttle@rottlundhomes.com> writes:
> I am not able to run any user created functions.
> [ plpgsql dumps core ]
My bet would be that you have a plpgsql.so library that is mismatched
with your postgres server. It's fairly easy to get into such situations
if you've upgraded your PG version, or made a custom PG installation
without being careful to get rid of the one that comes preinstalled.
You didn't give enough details to say exactly what to do to fix it, though.
regards, tom lane
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: PL/pgsql
@ 2004-07-01 23:27 Bender, Cheryl <cbender@mriresearch.org>
0 siblings, 0 replies; 8+ messages in thread
From: Bender, Cheryl @ 2004-07-01 23:27 UTC (permalink / raw)
To: Tuttle, Gene <cetuttle@rottlundhomes.com>; pgsql-admin
Gene;
This is probably not much help but I was able to create and run this
function on 7.3.2 (running on FreeBSD 5.1) verbatim from your message.
Perhaps the function is corrupted? Maybe try dropping and recreating
the function.
Of course you wouldn't be able to factorial anything bigger than about
11 or 12 using this function because of the int4 datatype, but this
shouldn't be an issue with an arg of 1. Even feeding a number like 20
that caused an overflow did not cause my server to crash--just returned
a negative number.
Cheryl Bender
-----Original Message-----
From: pgsql-admin-owner@postgresql.org
[mailto:pgsql-admin-owner@postgresql.org] On Behalf Of Tuttle, Gene
Sent: Tuesday, June 29, 2004 3:53 PM
To: 'pgsql-admin@postgresql.org'
Subject: [ADMIN] PL/pgsql
I am new to Postgresql and am having problems getting functions working.
I have been following an example in the book "PostgreSQL" by Douglas &
Douglas Published by Developers Library. I put the code in as a
function (see end of email)
When I execute it from psql I get the following:
dev=# select my_factorial(1);
server closed the connection unexpectedly
This probably means the server terminated abnormally
before or while processing the request.
The connection to the server was lost. Attempting reset: Failed.
Can any one help me find the problem. point me in the right direction?
Thanks
Gene Tuttle
-- Function: public.my_factorial(int4)
-- DROP FUNCTION public.my_factorial(int4);
CREATE OR REPLACE FUNCTION public.my_factorial(int4)
RETURNS int4 AS
'
DECLARE
arg INTEGER;
BEGIN
arg := $1;
IF arg IS NULL or arg < 0 THEN
RAISE NOTICE \'Invalid Number\';
RETURN NULL;
ELSE
IF arg = 1 THEN
RETURN 1;
ELSE
DECLARE
next_value INTEGER;
BEGIN
next_value := my_factorial(arg - 1) *
arg;
RETURN next_value;
END;
END IF;
END IF;
END;
'
LANGUAGE 'plpgsql' VOLATILE;
---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend
^ permalink raw reply [nested|flat] 8+ messages in thread
end of thread, other threads:[~2004-07-01 23:27 UTC | newest]
Thread overview: 8+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2001-03-02 15:55 PL/pgSQL Oliver Vecernik <vecernik@aon.at>
2001-03-02 16:57 ` Stephan Szabo <sszabo@megazone23.bigpanda.com>
2001-03-02 19:01 ` Richard Bayet <bayet@enseirb.fr>
2002-07-26 13:52 PL/pgSQL Heni Lolov <hal_bg@yahoo.com>
2004-06-29 20:52 PL/pgsql Tuttle, Gene <cetuttle@rottlundhomes.com>
2004-06-30 13:58 PL/pgSQL Tuttle, Gene <cetuttle@rottlundhomes.com>
2004-06-30 16:03 ` Tom Lane <tgl@sss.pgh.pa.us>
2004-07-01 23:27 Re: PL/pgsql Bender, Cheryl <cbender@mriresearch.org>
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