pg.ddx.io  pgsql-admin@postgresql.org mailing list archive  
help / color / mirror / Atom feed
PL/pgSQL
8+ messages / 7 participants
[nested] [flat]

* PL/pgSQL
@ 2001-03-02 15:55  Oliver Vecernik <vecernik@aon.at>
  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 16:57  Stephan Szabo <sszabo@megazone23.bigpanda.com>
  parent: Oliver Vecernik <vecernik@aon.at>
  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 19:01  Richard Bayet <bayet@enseirb.fr>
  parent: Stephan Szabo <sszabo@megazone23.bigpanda.com>
  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>
  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 16:03  Tom Lane <tgl@sss.pgh.pa.us>
  parent: Tuttle, Gene <cetuttle@rottlundhomes.com>
  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