Re: [GENERAL] Stored procedures and schema renames

[Florian Weimer]

On 04/10/2014 03:29 PM, Rob Sargent wrote:


Code for db functions should be a repository. Easy edit and rerun


Well, not necessarily inside the transaction that renames the schema.

I've settled for this inside the transaction (running as a superuser):

   UPDATE pg_proc
  SET proconfig = '{"search_path=symboldb, public"}'
  WHERE pronamespace = (SELECT oid FROM pg_namespace
WHERE nspname = 'symboldb');


--
Florian Weimer / Red Hat Product Security Team


--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Stored procedures and schema renames

[Rob Sargent]

Code for db functions should be a repository. Easy edit and rerun

Sent from my iPhone

> On Apr 10, 2014, at 6:19 AM, Florian Weimer  wrote:
> 
> I would like to rename schemas without breaking the stored procedures in 
> them.  Currently, this does not work if the stored procedure definition 
> contains a schema self-reference because that does not get renamed.  I tried 
> "SET search_path FROM CURRENT", but that seems to expand the search path on 
> function definition time, which does not address this issue either.
> 
> If backend code changes are required, what would be a reasonable way to 
> approach this?  Would adding a CURRENT_SCHEMA pseudo-schema which can be used 
> in stored procedures work?
> 
> -- 
> Florian Weimer / Red Hat Product Security Team
> 
> 
> -- 
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] Stored procedures and schema renames

[Florian Weimer]

I would like to rename schemas without breaking the stored procedures in 
them.  Currently, this does not work if the stored procedure definition 
contains a schema self-reference because that does not get renamed.  I 
tried "SET search_path FROM CURRENT", but that seems to expand the 
search path on function definition time, which does not address this 
issue either.


If backend code changes are required, what would be a reasonable way to 
approach this?  Would adding a CURRENT_SCHEMA pseudo-schema which can be 
used in stored procedures work?


--
Florian Weimer / Red Hat Product Security Team


--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] stored procedures (packages)

[Albe Laurenz]

J.V. wrote:
> I need to run a series of stored procedures, what is the best way to
> organize and run.
> 
> Ideally would like something like Oracle PL/SQL where I can put all
> methods in one file and create a main() method
> 
> then just :  select main() to have them all run.
> 
> If there is a way to do this, please let me know.

Don't try to do PostgreSQL things the Oracle way.

To group functions that logically belong together, you can put them
into a separate schema.

You can grant users execute rights on only one of them if you like
(or rather, because by default PUBLIC has execute rights, revoke
that from all others).

> Also is it possible to call a stored function or procedure from
another
> stored proc/function?

Sure, use them in an SQL statement inside a function.

Yours,
Laurenz Albe

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] stored procedures (packages)

[John R Pierce]

On 09/30/11 12:57 AM, J.V. wrote:
I need to run a series of stored procedures, what is the best way to 
organize and run.


Ideally would like something like Oracle PL/SQL where I can put all 
methods in one file and create a main() method


then just :  select main() to have them all run.


in function main, have

select function1();
select function2();
...


Also is it possible to call a stored function or procedure from 
another stored proc/function?



see above.

note that postgres doesn't acctually have stored procedures, it only has 
functions.


--
john r pierceN 37, W 122
santa cruz ca mid-left coast


--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] stored procedures (packages)

[J.V.]

I need to run a series of stored procedures, what is the best way to 
organize and run.


Ideally would like something like Oracle PL/SQL where I can put all 
methods in one file and create a main() method


then just :  select main() to have them all run.

If there is a way to do this, please let me know.

Also is it possible to call a stored function or procedure from another 
stored proc/function?



--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Stored procedures in C

[Emiliano Moscato]

Guys, it has nothing to do with my question :D
I don't know why Martin answer to me anything about Cobol.
I just was looking how to query database in a stored procedure in C. Some
people pointed me to SPI documentation (at chapter 41 of oficial Postgres
documentation) that is being useful.
Thanks for worry about it :)

Emiliano

2008/4/24 Merlin Moncure <[EMAIL PROTECTED]>:

> On Wed, Apr 23, 2008 at 6:38 PM, Martin Gainty <[EMAIL PROTECTED]>
> wrote:
> >
> >
> > Emiliano and Mike
> >
> > The real challenge is trying to determine what a datatype is in
> cobol..for
> > that matter what is stack variable or heap in Cobol?
> > In the end you're better off writing this mess (preferably in Java)..
> > unless of course you need the billable hours for
> > the first rewrite to C
> > then later rewrite to Java
>
> (have no idea how this relates to the OP's original question).  If you
> are trying to port a cobol app to postgres, your best bet is to go
> through the client interface, libpq.  If you had to do it on the
> server side, I would stick to cobol environments that are C ABI
> compatible.  Writing general purpose data procedures in C is just not
> a very good idea most of the time...it's difficult and dangerous...C
> SPI has great uses, it's just not for everything.
>
> I personally think cobol is better suited for data processing type
> problems than java.  Mapping cobol data types to SQL is not terribly
> difficult.  cobol is notoriously difficult to port to another
> langauges...probably cheaper to connect it to the database via ISAM
> wrapper if the app is over a certain size.  Many modern cobol
> environments support external data sources through various
> techniques...extfh for example.  AcuCobol (crypticly) allows linking a
> ISAM emulation layer directly to the cobol runtime, one approach I've
> used in the past.
>
> merlin
>



-- 
mOsKi
"No hay nada que uno haga mal , lo que hay es poco vino." Autor Anonimo

Re: [GENERAL] Stored procedures in C

[Merlin Moncure]

On Wed, Apr 23, 2008 at 6:38 PM, Martin Gainty <[EMAIL PROTECTED]> wrote:
>
>
> Emiliano and Mike
>
> The real challenge is trying to determine what a datatype is in cobol..for
> that matter what is stack variable or heap in Cobol?
> In the end you're better off writing this mess (preferably in Java)..
> unless of course you need the billable hours for
> the first rewrite to C
> then later rewrite to Java

(have no idea how this relates to the OP's original question).  If you
are trying to port a cobol app to postgres, your best bet is to go
through the client interface, libpq.  If you had to do it on the
server side, I would stick to cobol environments that are C ABI
compatible.  Writing general purpose data procedures in C is just not
a very good idea most of the time...it's difficult and dangerous...C
SPI has great uses, it's just not for everything.

I personally think cobol is better suited for data processing type
problems than java.  Mapping cobol data types to SQL is not terribly
difficult.  cobol is notoriously difficult to port to another
langauges...probably cheaper to connect it to the database via ISAM
wrapper if the app is over a certain size.  Many modern cobol
environments support external data sources through various
techniques...extfh for example.  AcuCobol (crypticly) allows linking a
ISAM emulation layer directly to the cobol runtime, one approach I've
used in the past.

merlin

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Stored procedures in C

[Martin Gainty]

Good Morning Emiliano-

since postgres is written in 'C'
and Most of us on this list have programmed in C ..although my experience was 
'used in last millenia'

if we reference contrib/query/tsearch2/query.c

when you see  statements such as
PG_FUNCTION_INFO_V1(tsquery_in);

you are calling a header file at \include\server\fmgr\fmgr.h which is 
macro-subbing at 
specifically:

#define PG_FUNCTION_INFO_V1(funcname) \
extern Pg_finfo_record * CppConcat(pg_finfo_,funcname) (void); \
Pg_finfo_record * \
CppConcat(pg_finfo_,funcname) (void) \
{ \
 static Pg_finfo_record my_finfo = { 1 }; \
 return &my_finfo; \
} \
extern int no_such_variable

In any case please feel free to ping us for any questions you might have
It sounds like you have quite a challenge ahead of you

Buena Suerte
Martin-
  - Original Message - 
  From: Emiliano Moscato 
  To: Andrej Ricnik-Bay 
  Cc: pgsql-general@postgresql.org 
  Sent: Thursday, April 24, 2008 8:44 AM
  Subject: Re: [GENERAL] Stored procedures in C


  Thanks a lot for the response Andrej!

  One of these texts was known for me. But all of them are VERY basic. No one 
explains how to do a query and manage results :(
  The only one that manage querys is the source code placed at 
contrib/tablefunc in the Postgres distribution. But is VERY hard to follow for 
me :( I would like to find an intermediate step.
  Thanks again

  Emiliano


  2008/4/23 Andrej Ricnik-Bay <[EMAIL PROTECTED]>:

On 24/04/2008, Emiliano Moscato <[EMAIL PROTECTED]> wrote:
> I have to do some stuff writing stored procedures for Postgres in C. I saw
> the oficial documentation but it was hard for me to find out how to do a
> simple function, let's call it "query()" , that receives a string and uses
> this string to do a query and return the results. Has anyone some 
examples?


You don't mentioned where else you looked - did you come across these?

http://linuxgazette.net/139/peterson.html

http://www.faqs.org/docs/ppbook/x15284.htm


http://www.observercentral.net/~selkovjr/postgres/tutorial/html/exttut-getstart.html




> Thanks in advance...
> Regards,
>
> Emiliano


Cheers,
Andrej


--
Please don't top post, and don't use HTML e-Mail :}  Make your quotes 
concise.

http://www.american.edu/econ/notes/htmlmail.htm




  -- 
  mOsKi
  "No hay nada que uno haga mal , lo que hay es poco vino." Autor Anonimo 

Re: [GENERAL] Stored procedures in C

[Emiliano Moscato]

Thanks a lot for the response Andrej!

One of these texts was known for me. But all of them are VERY basic. No one
explains how to do a query and manage results :(
The only one that manage querys is the source code placed at
contrib/tablefunc in the Postgres distribution. But is VERY hard to follow
for me :( I would like to find an intermediate step.
Thanks again

Emiliano

2008/4/23 Andrej Ricnik-Bay <[EMAIL PROTECTED]>:

> On 24/04/2008, Emiliano Moscato <[EMAIL PROTECTED]> wrote:
> > I have to do some stuff writing stored procedures for Postgres in C. I
> saw
> > the oficial documentation but it was hard for me to find out how to do a
> > simple function, let's call it "query()" , that receives a string and
> uses
> > this string to do a query and return the results. Has anyone some
> examples?
>
> You don't mentioned where else you looked - did you come across these?
>
> http://linuxgazette.net/139/peterson.html
>
> http://www.faqs.org/docs/ppbook/x15284.htm
>
>
> http://www.observercentral.net/~selkovjr/postgres/tutorial/html/exttut-getstart.html
>
>
>
> > Thanks in advance...
> > Regards,
> >
> > Emiliano
>
> Cheers,
> Andrej
>
>
> --
> Please don't top post, and don't use HTML e-Mail :}  Make your quotes
> concise.
>
> http://www.american.edu/econ/notes/htmlmail.htm
>



-- 
mOsKi
"No hay nada que uno haga mal , lo que hay es poco vino." Autor Anonimo

Re: [GENERAL] Stored procedures in C

[Andrej Ricnik-Bay]

On 24/04/2008, Emiliano Moscato <[EMAIL PROTECTED]> wrote:
> I have to do some stuff writing stored procedures for Postgres in C. I saw
> the oficial documentation but it was hard for me to find out how to do a
> simple function, let's call it "query()" , that receives a string and uses
> this string to do a query and return the results. Has anyone some examples?

You don't mentioned where else you looked - did you come across these?

http://linuxgazette.net/139/peterson.html

http://www.faqs.org/docs/ppbook/x15284.htm

http://www.observercentral.net/~selkovjr/postgres/tutorial/html/exttut-getstart.html



> Thanks in advance...
> Regards,
>
> Emiliano

Cheers,
Andrej


-- 
Please don't top post, and don't use HTML e-Mail :}  Make your quotes concise.

http://www.american.edu/econ/notes/htmlmail.htm

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Stored procedures in C

[Martin Gainty]

Emiliano and Mike

The real challenge is trying to determine what a datatype is in cobol..for that 
matter what is stack variable or heap in Cobol?
In the end you're better off writing this mess (preferably in Java)..
unless of course you need the billable hours for 
the first rewrite to C 
then later rewrite to Java

(That should get you at least 2 weeks worth of work..)

Buena Suerte/Good Luck
Martin
  - Original Message - 
  From: Emiliano Moscato 
  To: pgsql-general@postgresql.org 
  Sent: Wednesday, April 23, 2008 3:32 PM
  Subject: [GENERAL] Stored procedures in C


  Hi all,

  I have to do some stuff writing stored procedures for Postgres in C. I saw 
the oficial documentation but it was hard for me to find out how to do a simple 
function, let's call it "query()" , that receives a string and uses this string 
to do a query and return the results. Has anyone some examples?
  I am looking at crosstab() example at contrib/tablefunc/tablefunc.c, but it 
is hard to follow, cause it does more complicated stuff.
  Thanks in advance...
  Regards,

  Emiliano
  -- 
  mOsKi
  "No hay nada que uno haga mal , lo que hay es poco vino." Autor Anonimo 

[GENERAL] Stored procedures in C

[Emiliano Moscato]

Hi all,

I have to do some stuff writing stored procedures for Postgres in C. I saw
the oficial documentation but it was hard for me to find out how to do a
simple function, let's call it "query()" , that receives a string and uses
this string to do a query and return the results. Has anyone some examples?
I am looking at crosstab() example at contrib/tablefunc/tablefunc.c, but it
is hard to follow, cause it does more complicated stuff.
Thanks in advance...
Regards,

Emiliano
-- 
mOsKi
"No hay nada que uno haga mal , lo que hay es poco vino." Autor Anonimo

Re: [GENERAL] Stored procedures when and how: was: Sun acquires MySQL

[brian]

Pavel Stehule wrote:



> ...


bottleneck is in repeated assign s := s || ..

I will try trick:

create or replace function list(int)
returns varchar as $$
 begin
   return array_to_string(array(select '' || i || ''
from generate_series(1, $1) g(i)), '');
 end$$ language plpgsql immutable;

test
100, 1.3ms
1000, 7.64ms
1, 63ms -- nice I don't need C
10, 350ms
Regards

Pavel Stehule



That's some trick! Thanks for the lessons, Pavel.

b

---(end of broadcast)---
TIP 6: explain analyze is your friend

Re: [GENERAL] Stored procedures when and how: was: Sun acquires MySQL

[Pavel Stehule]

>
> Yep, the more I read, the more I get confused.
> Java loading overhead is a common myth (I can't say if true or false),
> and what Tom writes above can find a tentative place in my mind.
> But still then I can't understand where plsql should or shouldn't be
> used.
>
> I really would enjoy to see some general guideline on how to chose.
>

1. use procedure lot of SQL statements --> use plpgsql
2. procedure needs some untrusted functionality -> use untrusted language
3. procedure contains only expressions
3.a) isn't too much important --> use plpgsql don't forgot IMMUTABLE flag
3.b) is important and is bottleneck --> try perl
3.c) is most important or is wide used --> use C
3.d) is simply implemented in C (some time, string fce) --> use C

learn some trick:

create or replace function list(int)
returns varchar as $$
declare s varchar = '';
begin
 for i in 1..$1 loop
   s := s || '' || i || '';
 end loop;
 return s;
end; $$ language plpgsql;

postgres=# select list(10);

list
---
 
12345678910
(1 row)

Time: 0,927 ms -- well

number, time
100, 5ms
1000, 75ms   ...  usable
1, 4s ... slow

so if I use fce list with param < 1000 I can use plpgsql without any
problems. With bigger value I have problem. But I forgot IMMUTABLE,
ook try again:

100, 4ms
1000, 70ms
1, 3.8s   ok IMMUTABLE doesn't help here

what is bottleneck? FOR?

create or replace function list(int)
returns varchar as $$
declare s varchar = '';
 begin
   for i in 1..$1 loop
 perform  '' || i || '';
   end loop;
   return s;
end; $$ language plpgsql immutable;

1, 443 ms ..

bottleneck is in repeated assign s := s || ..

I will try trick:

create or replace function list(int)
returns varchar as $$
 begin
   return array_to_string(array(select '' || i || ''
from generate_series(1, $1) g(i)), '');
 end$$ language plpgsql immutable;

test
100, 1.3ms
1000, 7.64ms
1, 63ms -- nice I don't need C
10, 350ms
Regards

Pavel Stehule


> thanks
>
> --
> Ivan Sergio Borgonovo
> http://www.webthatworks.it
>
>
> ---(end of broadcast)---
> TIP 4: Have you searched our list archives?
>
>http://archives.postgresql.org/
>

---(end of broadcast)---
TIP 9: In versions below 8.0, the planner will ignore your desire to
   choose an index scan if your joining column's datatypes do not
   match

Re: [GENERAL] Stored procedures when and how: was: Sun acquires MySQL

[Merlin Moncure]

On Jan 22, 2008 2:24 AM, Ivan Sergio Borgonovo <[EMAIL PROTECTED]> wrote:
> > > I doubt that what you were measuring there was either procedure
> > > call overhead or java computational speed; more likely it was the
> > > cost of calling back out of java, through pl/java's JDBC
> > > emulation, down through SPI, to re-execute the same INSERT that
> > > you then decided to execute directly.  In particular, if
> > > pl/java's JDBC doesn't know anything about caching query plans,
> > > performance for simple inserts could be expected to go into the
> > > tank just because of that.  (Whether it actually does or not, I
> > > have no idea --- but I would expect it to be a lot less mature
> > > than the mainstream JDBC driver for PG, and that took years to
> > > get smart about prepared queries ...)
>
> > > Without knowing where the bottleneck actually is, it's
> > > unreasonable to assume that it would hurt a different use-case.
>
> > Tom,
> > I have read several of your post on store procedure performance.
> > Why not give us your take on what works and what does not.
>
> Yep, the more I read, the more I get confused.
> Java loading overhead is a common myth (I can't say if true or false),
> and what Tom writes above can find a tentative place in my mind.
> But still then I can't understand where plsql should or shouldn't be
> used.

It's fairly trivial to test performance of functions vs. raw
statements, or just about anything going on with the server.  The
benchmarking tool, pgbench, allows custom sql which is great for
things like this.  It would have shown you that functions themselves
are not the reason why your application was not running quickly.  My
seat of the pants guess (I don't do java) was that your problem was in
the jdbc driver somewhere.  When using a high level database framework
like jdbc or ado.net, it can be difficult to figure out exactly what
is going on with the database at times...I tend to avoid them.

merlin

---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings

[GENERAL] Stored procedures when and how: was: Sun acquires MySQL

[Ivan Sergio Borgonovo]

On Mon, 21 Jan 2008 21:31:23 -0800
johnf <[EMAIL PROTECTED]> wrote:

> On Monday 21 January 2008 04:47:40 pm Tom Lane wrote:

> > I doubt that what you were measuring there was either procedure
> > call overhead or java computational speed; more likely it was the
> > cost of calling back out of java, through pl/java's JDBC
> > emulation, down through SPI, to re-execute the same INSERT that
> > you then decided to execute directly.  In particular, if
> > pl/java's JDBC doesn't know anything about caching query plans,
> > performance for simple inserts could be expected to go into the
> > tank just because of that.  (Whether it actually does or not, I
> > have no idea --- but I would expect it to be a lot less mature
> > than the mainstream JDBC driver for PG, and that took years to
> > get smart about prepared queries ...)

> > Without knowing where the bottleneck actually is, it's
> > unreasonable to assume that it would hurt a different use-case.

> Tom,
> I have read several of your post on store procedure performance.
> Why not give us your take on what works and what does not.

Yep, the more I read, the more I get confused.
Java loading overhead is a common myth (I can't say if true or false),
and what Tom writes above can find a tentative place in my mind.
But still then I can't understand where plsql should or shouldn't be
used.

I really would enjoy to see some general guideline on how to chose.

thanks

-- 
Ivan Sergio Borgonovo
http://www.webthatworks.it


---(end of broadcast)---
TIP 4: Have you searched our list archives?

   http://archives.postgresql.org/

Re: [GENERAL] stored procedures and dynamic queries

[Obe, Regina]

 As a side note, there is actually a book on design patterns in SQL,
although I personally haven't read it. From the reviews I recall reading
about it, I think its mostly based on Oracle Features.  Still might be a
good read as far as PostgreSQL is concerned except for the sections on
Graphs and recursive trees since Oracle has special syntactical sugar
for that kind of stuff that is unique to Oracle.

http://www.rampant-books.com/book_2006_1_sql_coding_styles.htm

Hope that helps,
Regina



-Original Message-
From: [EMAIL PROTECTED]
[mailto:[EMAIL PROTECTED] On Behalf Of Ted Byers
Sent: Tuesday, December 04, 2007 9:59 AM
To: Richard Huxton; Ivan Sergio Borgonovo
Cc: pgsql-general@postgresql.org
Subject: Re: [GENERAL] stored procedures and dynamic queries

--- Richard Huxton <[EMAIL PROTECTED]> wrote:
> Ivan Sergio Borgonovo wrote:
> > On Tue, 04 Dec 2007 08:14:56 +
> > Richard Huxton <[EMAIL PROTECTED]> wrote:
> > 
> >> Unless it's an obvious decision (millions of
> small identical
> >> queries vs. occasional large complex ones) then
> you'll have to
> >> test. That's going to be true of any decision
> like this on any
> >> system.
> > 
> > :(
> > 
> > I'm trying to grasp a general idea from the view
> point of a developer
> > rather than a sysadmin. At this moment I'm not
> interested in
> > optimisation, I'm interested in understanding the
> trade off of
> > certain decisions in the face of a cleaner
> interface.
> 
> Always go for the cleaner design. If it turns out
> that isn't fast 
> enough, *then* start worrying about having a bad but
> faster design.
> 
I don't know about "clean", but the mantra here is
"First you make it provably correct, then you make it
fast."

I am a fan of making things as simple as practicable,
BUT NO SIMPLER. I don't know how that meshes with
"clean", but the general idea is right.

If you look at my code, my C++ and my java code is
infinitely better than my SQL, so with either of
those, I end up making fewer variants, and my starting
point is generally much closer to my end point, and
that is just a function of experience.  With my SQL
code, I generally find myself producing a variety of
scripts to support a given task.  And these include
exploration of just about everything SQL has to offer.
 I TRY everything, from joins to correlated subqueries
to stored procedures to views, and beyond.  And I am
still trying to develop an intuition as to which
options will serve best in a given situation,
analogous to design patterns I routinely use in my C++
and Java code.

Your reaction to Richard's advice to test seems to
imply you want a more direct approach to "THE" answer.
 That is chasing something that doesn't exist!  I tell
you, as a developer (and NOT a sysadmin), there is no
substitute for experience and THAT can ONLY be
obtained through trial and error.  I haven't seen any
books about design patterns in SQL (while there are
plenty in C++ and Java, and other languages), so there
is no short cut.

In short, the general idea most developers I know work
with is "code and test, and then code again and test
again, until you've tried the variety of options that
exist."

There is no substitute for testing yourself.  I have
seen repeated advice to replace correlated subqueries
by left joins, claiming that ubiquitous experience is
that the joins are faster, and yet, in my tests, in
most cases there was little difference in speed while
in others the correlated subqueries were faster.  So
it appears the advice was based on experience with
dated software and the RDBMS in question had
subsequently dramatically improved how it handled
correlated subqueries.  And my use of EXPLAIN
confirmed what I was finding through testing.

> > Most of the documents available are from a
> sysadmin point of view.
> > That makes me think that unless I write terrible
> SQL it won't make a
> > big difference and the first place I'll have to
> look at if the
> > application need to run faster is pg config.
> 
> The whole point of a RDBMS is so that you don't have
> to worry about 
> this. If you have to start tweaking the fine details
> of these things, 
> then that's a point where the RDBMS has reached its
> limits. In a perfect 
> world you wouldn't need to configure PG either, but
> it's not that clever 
> I'm afraid.
> 
I am not sure I buy this, if I properly understand it.
 Trust me, I have written some really bad but simple
queries that took hours to complete a task that was
completed in less than a minute with smarter code. 
And yet the "bad" code I'd written was similar in
nature to examples used in some tex

Re: [GENERAL] stored procedures and dynamic queries

[Ivan Sergio Borgonovo]

On Tue, 04 Dec 2007 13:54:15 +
Richard Huxton <[EMAIL PROTECTED]> wrote:

> Always go for the cleaner design. If it turns out that isn't fast 
> enough, *then* start worrying about having a bad but faster design.

mmm yeah right. I did express myself badly.
What I mean I've first to know what are the boundaries to know what
a good design is.
I'm ready to refactor... I'd just like to avoid it for ignorance of
common knowledge about good practice.

BTW still a good reading for dev:

http://www.gtsm.com/oscon2004/

> > Most of the documents available are from a sysadmin point of view.
> > That makes me think that unless I write terrible SQL it won't
> > make a big difference and the first place I'll have to look at if
> > the application need to run faster is pg config.

> The whole point of a RDBMS is so that you don't have to worry about 
> this. If you have to start tweaking the fine details of these

This will definitively be the last resort. These times you can't wear
so many hats as before.

> Keep your database design clean, likewise with your queries,
> consider whether you can cache certain results and get everything
> working first.

At the end... if you don't look to much to details everything will
reach a defined deterministic state after all ;)

> Note that this is quite old now, so some performance-related
> assumptions will be wrong for current versions of PG.

I noticed. Maybe this will be part of some other question later.

-- 
Ivan Sergio Borgonovo
http://www.webthatworks.it


---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster

Re: [GENERAL] stored procedures and dynamic queries

[Ted Byers]

--- Richard Huxton <[EMAIL PROTECTED]> wrote:
> Ivan Sergio Borgonovo wrote:
> > On Tue, 04 Dec 2007 08:14:56 +
> > Richard Huxton <[EMAIL PROTECTED]> wrote:
> > 
> >> Unless it's an obvious decision (millions of
> small identical
> >> queries vs. occasional large complex ones) then
> you'll have to
> >> test. That's going to be true of any decision
> like this on any
> >> system.
> > 
> > :(
> > 
> > I'm trying to grasp a general idea from the view
> point of a developer
> > rather than a sysadmin. At this moment I'm not
> interested in
> > optimisation, I'm interested in understanding the
> trade off of
> > certain decisions in the face of a cleaner
> interface.
> 
> Always go for the cleaner design. If it turns out
> that isn't fast 
> enough, *then* start worrying about having a bad but
> faster design.
> 
I don't know about "clean", but the mantra here is
"First you make it provably correct, then you make it
fast."

I am a fan of making things as simple as practicable,
BUT NO SIMPLER. I don't know how that meshes with
"clean", but the general idea is right.

If you look at my code, my C++ and my java code is
infinitely better than my SQL, so with either of
those, I end up making fewer variants, and my starting
point is generally much closer to my end point, and
that is just a function of experience.  With my SQL
code, I generally find myself producing a variety of
scripts to support a given task.  And these include
exploration of just about everything SQL has to offer.
 I TRY everything, from joins to correlated subqueries
to stored procedures to views, and beyond.  And I am
still trying to develop an intuition as to which
options will serve best in a given situation,
analogous to design patterns I routinely use in my C++
and Java code.

Your reaction to Richard's advice to test seems to
imply you want a more direct approach to "THE" answer.
 That is chasing something that doesn't exist!  I tell
you, as a developer (and NOT a sysadmin), there is no
substitute for experience and THAT can ONLY be
obtained through trial and error.  I haven't seen any
books about design patterns in SQL (while there are
plenty in C++ and Java, and other languages), so there
is no short cut.

In short, the general idea most developers I know work
with is "code and test, and then code again and test
again, until you've tried the variety of options that
exist."

There is no substitute for testing yourself.  I have
seen repeated advice to replace correlated subqueries
by left joins, claiming that ubiquitous experience is
that the joins are faster, and yet, in my tests, in
most cases there was little difference in speed while
in others the correlated subqueries were faster.  So
it appears the advice was based on experience with
dated software and the RDBMS in question had
subsequently dramatically improved how it handled
correlated subqueries.  And my use of EXPLAIN
confirmed what I was finding through testing.

> > Most of the documents available are from a
> sysadmin point of view.
> > That makes me think that unless I write terrible
> SQL it won't make a
> > big difference and the first place I'll have to
> look at if the
> > application need to run faster is pg config.
> 
> The whole point of a RDBMS is so that you don't have
> to worry about 
> this. If you have to start tweaking the fine details
> of these things, 
> then that's a point where the RDBMS has reached its
> limits. In a perfect 
> world you wouldn't need to configure PG either, but
> it's not that clever 
> I'm afraid.
> 
I am not sure I buy this, if I properly understand it.
 Trust me, I have written some really bad but simple
queries that took hours to complete a task that was
completed in less than a minute with smarter code. 
And yet the "bad" code I'd written was similar in
nature to examples used in some texts to explain ideas
in SQL.  The point is, until you get extensive
experience in SQL programming and optimization, you
won't know what is bad code until you test it.

Personally, I rely on the sysadmin to administer the
RDBMS properly, to ensure it is configured
appropriately for our application, AND I ask his or
her advice and input on how I design and implement my
SQL code, as well as for input on distributed
application architecture.  You can't do it all.  On my
development machine, I just use whatever the default
configuration is, so I have it up and running in a
flash and can focus on my development.  I'll change
that configuration ONLY if the sysadmin tells me there
is a problem with the default.  My advice, therefore
is forget about configuration issues and focus on
getting your SQL right, and then fast, and let your
sysadmin advise, and possibly help, with changes to
your configuration should he or she feel it needs to
be modified to better represent how your application
will behave once in production.

> Keep your database design clean, likewise with your
> queries, consider 
> whether you can cache certain results and get
> everything w

Re: [GENERAL] stored procedures and dynamic queries

[Richard Huxton]

Ivan Sergio Borgonovo wrote:

On Tue, 04 Dec 2007 08:14:56 +
Richard Huxton <[EMAIL PROTECTED]> wrote:


Unless it's an obvious decision (millions of small identical
queries vs. occasional large complex ones) then you'll have to
test. That's going to be true of any decision like this on any
system.


:(

I'm trying to grasp a general idea from the view point of a developer
rather than a sysadmin. At this moment I'm not interested in
optimisation, I'm interested in understanding the trade off of
certain decisions in the face of a cleaner interface.


Always go for the cleaner design. If it turns out that isn't fast 
enough, *then* start worrying about having a bad but faster design.



Most of the documents available are from a sysadmin point of view.
That makes me think that unless I write terrible SQL it won't make a
big difference and the first place I'll have to look at if the
application need to run faster is pg config.


The whole point of a RDBMS is so that you don't have to worry about 
this. If you have to start tweaking the fine details of these things, 
then that's a point where the RDBMS has reached its limits. In a perfect 
world you wouldn't need to configure PG either, but it's not that clever 
I'm afraid.


Keep your database design clean, likewise with your queries, consider 
whether you can cache certain results and get everything working first.


Then, look for where bottle-necks are, do you have any unexpectedly 
long-running queries? (see www.pgfoundry.org for some tools to help with 
log analysis)



This part (for posterity) looks as the most interesting for
developers:
http://www.gtsm.com/oscon2003/toc.html
Starting from Functions


Note that this is quite old now, so some performance-related assumptions 
will be wrong for current versions of PG.



Still I can't understand some things, I'll come back.



--
  Richard Huxton
  Archonet Ltd

---(end of broadcast)---
TIP 4: Have you searched our list archives?

  http://archives.postgresql.org/

Re: [GENERAL] stored procedures and dynamic queries

[Ivan Sergio Borgonovo]

On Tue, 04 Dec 2007 08:14:56 +
Richard Huxton <[EMAIL PROTECTED]> wrote:

> Unless it's an obvious decision (millions of small identical
> queries vs. occasional large complex ones) then you'll have to
> test. That's going to be true of any decision like this on any
> system.

:(

I'm trying to grasp a general idea from the view point of a developer
rather than a sysadmin. At this moment I'm not interested in
optimisation, I'm interested in understanding the trade off of
certain decisions in the face of a cleaner interface.

Most of the documents available are from a sysadmin point of view.
That makes me think that unless I write terrible SQL it won't make a
big difference and the first place I'll have to look at if the
application need to run faster is pg config.

This part (for posterity) looks as the most interesting for
developers:
http://www.gtsm.com/oscon2003/toc.html
Starting from Functions

Still I can't understand some things, I'll come back.

thanks for the right pointers.

-- 
Ivan Sergio Borgonovo
http://www.webthatworks.it


---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster

Re: [GENERAL] stored procedures and dynamic queries

[Richard Huxton]

Ivan Sergio Borgonovo wrote:

On Mon, 03 Dec 2007 19:06:29 +
Richard Huxton <[EMAIL PROTECTED]> wrote:


Ivan Sergio Borgonovo wrote:

Any general rule about dynamically generated queries in stored
procedures vs. performances?

It's the same decision as any with any prepared plan vs
plan-each-time trade-off.


Should I guess this trade off on aruspices or is it possible to gain
a little bit of culture or it's something based on heuristic and
experience?


Unless it's an obvious decision (millions of small identical queries vs. 
occasional large complex ones) then you'll have to test. That's going to 
be true of any decision like this on any system.



If the second, any good reading?
How should I take into account parameters like:
- is it a insert/update vs select query
- is it performed on a large table?
- is it performed frequently? frequently with same parameters?
frequently with different parameters?
- does the table is growing?
- are there frequent delete?


Read the manuals, particularly the sections on MVCC, also the planner 
and EXPLAIN. Read up on the WAL and checkpoints.


You might want to skim through the archives on the performance list. You 
might find some of the community-related resources useful too. Some of 
the following might be a little out-of-date, so check:


http://www.westnet.com/~gsmith/content/postgresql/
http://www.powerpostgresql.com/PerfList
http://www.varlena.com/varlena/GeneralBits/Tidbits/index.php

HTH
--
  Richard Huxton
  Archonet Ltd

---(end of broadcast)---
TIP 4: Have you searched our list archives?

  http://archives.postgresql.org/

Re: [GENERAL] stored procedures and dynamic queries

[Ivan Sergio Borgonovo]

On Mon, 03 Dec 2007 19:06:29 +
Richard Huxton <[EMAIL PROTECTED]> wrote:

> Ivan Sergio Borgonovo wrote:
> > Any general rule about dynamically generated queries in stored
> > procedures vs. performances?
> 
> It's the same decision as any with any prepared plan vs
> plan-each-time trade-off.

Should I guess this trade off on aruspices or is it possible to gain
a little bit of culture or it's something based on heuristic and
experience?

If the second, any good reading?
How should I take into account parameters like:
- is it a insert/update vs select query
- is it performed on a large table?
- is it performed frequently? frequently with same parameters?
frequently with different parameters?
- does the table is growing?
- are there frequent delete?
etc...

I'm just guessing things that may impact.

> Of course, in your example the query wouldn't work at all - you'd
> need to use the EXECUTE command in plpgsql.

OK just adapted from a more complicated one and forgot to fix it.

thx

-- 
Ivan Sergio Borgonovo
http://www.webthatworks.it


---(end of broadcast)---
TIP 4: Have you searched our list archives?

   http://archives.postgresql.org/

Re: [GENERAL] stored procedures and dynamic queries

[Richard Huxton]

Ivan Sergio Borgonovo wrote:

Any general rule about dynamically generated queries in stored
procedures vs. performances?


It's the same decision as any with any prepared plan vs plan-each-time 
trade-off.


A query built using EXECUTE will have to be planned each time. That 
costs you something but means the plan will have all the information it 
needs.


A pre-planned query saves planning time on the second,third etc runs but 
the plan won't change with the query-parameters.


So:
A query that's going to be executed a million times in a loop with the 
same plan each time implies you want a pre-planned query.


A query executed once, or where changing input parameters would benefit 
from changing plans implies you want to re-plan each time.



If you don't have a good reason to think it matters one way or the 
other, then it probably doesn't. If it's not worth testing then it's not 
worth optimising either.



Of course, in your example the query wouldn't work at all - you'd need 
to use the EXECUTE command in plpgsql.


--
  Richard Huxton
  Archonet Ltd

---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster

[GENERAL] stored procedures and dynamic queries

[Ivan Sergio Borgonovo]

Any general rule about dynamically generated queries in stored
procedures vs. performances?

I was going to write stuff as simple as

create or replace function EditQty(int, int, int, varchar(10))
 returns boolean as
'
declare
_uid alias for $1;
_aid alias for $2;
_qty alias for $3;
_table alias $4;
_modified timestamp;
begin
_table := 'shop_commerce_basket' || _table
_modified := now();
update _table
set qty=_qty,
modified=_modified
where uid=_uid and aid=_aid;
if not found then
insert into _table (uid, aid, qty)
values(_uid,_aid,_qty);
end if;
end;
' language plpgsql;

Is it going to perform worse than with a static table name?

Where can I find some clue about the effects of similar decisions?

thx

-- 
Ivan Sergio Borgonovo
http://www.webthatworks.it


---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster

Re: [GENERAL] Stored Procedures vs Dynamic SQL generated by ORM ?

[Andrew Sullivan]

On Mon, Nov 26, 2007 at 11:57:37PM -0800, Alagu Madhu wrote:
> Which is the best Stored Procedures or Dynamic SQL generated by ORM ?

This is roughly similar to the question, "Is it better to travel by car,
boat, or plane?"  It rather depends on what you're trying to accomplish. 

A

-- 
Andrew Sullivan
Old sigs will return after re-constitution of blue smoke

---(end of broadcast)---
TIP 1: if posting/reading through Usenet, please send an appropriate
   subscribe-nomail command to [EMAIL PROTECTED] so that your
   message can get through to the mailing list cleanly

Re: [GENERAL] Stored Procedures vs Dynamic SQL generated by ORM ?

[Scott Marlowe]

On Nov 27, 2007 1:57 AM, Alagu Madhu <[EMAIL PROTECTED]> wrote:
>
> Which is the best Stored Procedures or Dynamic SQL generated by ORM ?

Depends on what you mean by best.  Dynamic SQL costs more in the
planning stage because the db has to plan out each statement before
running it.  However, it can consider the actual values in the queries
when creating those plans, so you get individually optimized plans for
each query.  Generally speaking Stored Procs (UDFs in pgsql) use
static query  / execution plans created at the time the UDF was
brought into being.

You should also considered prepared SQL from the client side as well.
Not sure if your ORM supports that or not.

Not a fan of ORM, but it's a necessary evil sometimes. :)

---(end of broadcast)---
TIP 4: Have you searched our list archives?

   http://archives.postgresql.org/

[GENERAL] Stored Procedures vs Dynamic SQL generated by ORM ?

[Alagu Madhu]

Hi All,


Which is the best Stored Procedures or Dynamic SQL generated by ORM ?



Thanks

Madhu Alagu

---(end of broadcast)---
TIP 6: explain analyze is your friend

Re: [GENERAL] Stored Procedures and Functions

[Albe Laurenz]

Harpreet Dhaliwal wrote:

> Is it true that postgres doesn't have a notion of Stored 
> Procedures and functions is what it has instead?
> RDBMS like Sql Server supports both stored procedures and functions. 
> So I was wondering what is the difference between a Stored 
> Procedure and a function. 

I think that your questions have not been answered yet.

Yes, it is true, PostgreSQL doesn't have procedures, only functions.

The difference between a function and a procedure is that the former
has a return value, while the latter does not. Procdures can hand back
results via output parameters.

The lack of procedures in PostgreSQL is mitigated by the fact that you
can achieve everything you need with a function:

- If you don't need to return results at all, you define a function
  with return type "void" (which means that nothing is returned).

- If you need to return more than one result, you can define a
  function with a composite return type (or equivalently with
  what PostgreSQL calls "output parameters").

Yours,
Laurenz Albe

---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?

   http://www.postgresql.org/docs/faq

Re: [GENERAL] Stored Procedures and Functions

[PFC]

MySQL supports procedures and functions.

Functions can return results but cannot update the database.
Procedures can update the database but cannot return results.

However :
- a function can call a procedure that updates the database !
- a procedure can return result through OUT parameters !

	It's a friggin mess. In pgsql, if you want, a STABLE or IMMUTABLE  
procedure is a function since it is repeatable : it will always return the  
same results with the same parameters, and has no side-effects. This is  
the definition of a function.


	It is better not to draw useless lines in the ground with huge "don't  
walk over this line" stickers. People will always find a way around.  
Better offer features that users need.




Is it true that postgres doesn't have a notion of Stored Procedures
and functions is what it has instead?
RDBMS like Sql Server supports both stored procedures and functions.
So I was wondering what is the difference between a Stored Procedure
and a function.


Pascal has functions and procedures. C has only functions. That doesn't
say anything about the relative usability of each language. Those are
just names.




---(end of broadcast)---
TIP 6: explain analyze is your friend

Re: [GENERAL] Stored Procedures and Functions

[Pavel Stehule]

Hello



Is it true that postgres doesn't have a notion of Stored Procedures and
functions is what it has instead?
RDBMS like Sql Server supports both stored procedures and functions.
So I was wondering what is the difference between a Stored Procedure and a
function.



It's true. PostgreSQL knows only functions. Difference between
procedures and function are in calling context a possibilities, and
depends on database system. Functions are called from SELECT
statements, procedures are called via statement CALL. On some systems
functions has some limits (it's not true for PostgreSQL). On some
systems procedures allow transaction con troll, returning
multirecordset (unbind selects), and more.

PostgreSQL implementation is more practical than elegant. PostgreSQL
functions hasn't classic limits, but some design points are little bit
dirty. When you start to play with OUT variables, you will see.

For beginner, difference between function and procedure is less than
small in PostgreSQL. There are two families of stored procedures:
Oracle and Microsoft. Look to their documentation.

Regards
Pavel Stehule

---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings

Re: [GENERAL] Stored Procedures and Functions

[Leif B. Kristensen]

On Saturday 2. June 2007 16:47, Harpreet Dhaliwal wrote:
>Hi,
>
>Is it true that postgres doesn't have a notion of Stored Procedures
> and functions is what it has instead?
>RDBMS like Sql Server supports both stored procedures and functions.
>So I was wondering what is the difference between a Stored Procedure
> and a function.

Pascal has functions and procedures. C has only functions. That doesn't 
say anything about the relative usability of each language. Those are 
just names.
-- 
Leif Biberg Kristensen | Registered Linux User #338009
http://solumslekt.org/ | Cruising with Gentoo/KDE
My Jazz Jukebox: http://www.last.fm/user/leifbk/

---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings

[GENERAL] Stored Procedures and Functions

[Harpreet Dhaliwal]

Hi,

Is it true that postgres doesn't have a notion of Stored Procedures and
functions is what it has instead?
RDBMS like Sql Server supports both stored procedures and functions.
So I was wondering what is the difference between a Stored Procedure and a
function.

Thanks,
~Harpreet

Re: [GENERAL] Stored Procedures Performance: Java vs. PL/Pgsql

[Chris Browne]

[EMAIL PROTECTED] (Daniel Kastenholz) writes:
> Hi,
>
> is there a rule of thumb or an estimate which language provides the
> best performance for stored procedures?
>
> I'm currently using PL/Pgsql, and wondering
> - to what extent these stored procedures are actually precompiled
> and/or optimized
> - if Java might provide better performance, or if parameter
> transformation and VM calls eat up the performance gains (if any)
>
> I'm aware that these questions can't be answered in a "universally
> valid" fashion, but I would appreciate your experiences.

The sorts of things I'd expect Java SPs to do better would be the
handling of complex arithmetic computations.

Notably, you can use native Java types to calculate things; doing the
equivalents in pl/pgsql would require iterating across sets of
interpreted SQL statements.

If your code is not "rich" in that, then Java won't provide advantage
in that area.

A place where I'd expect Java to be an anti-optimization (as compared
to pl/pgsql) is that there's little opportunity for the query
optimizer to penetrate into the code to be able to optimize usage of
the stored procedure.  As pl/pgsql is a pretty thin veneer atop SQL,
there is more that the query optimizer can do, analytically...

I'm speaking analytically here; I haven't used Java SPs.  I have used
various other SPs, though, and this sort of result does normally hold
true.
-- 
select 'cbbrowne' || '@' || 'cbbrowne.com';
http://www3.sympatico.ca/cbbrowne/emacs.html
(1) Sigs are preceded by the "sigdashes" line, ie "\n-- \n" (dash-dash-space).
(2) Sigs contain at least the name and address of the sender in the first line.
(3) Sigs are at most four lines and at most eighty characters per line.

---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster

[GENERAL] Stored Procedures Performance: Java vs. PL/Pgsql

[Daniel Kastenholz]

Hi,

is there a rule of thumb or an estimate which language provides the best 
performance for stored procedures?


I'm currently using PL/Pgsql, and wondering
- to what extent these stored procedures are actually precompiled and/or 
optimized
- if Java might provide better performance, or if parameter 
transformation and VM calls eat up the performance gains (if any)


I'm aware that these questions can't be answered in a "universally 
valid" fashion, but I would appreciate your experiences.


Thanks.

Daniel


---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings

Re: [GENERAL] stored procedures

[hubert depesz lubaczewski]

On 6/30/06, Alain Roger <[EMAIL PROTECTED]> wrote:
i'm migrating some SP from MySQL to PostgreSQL 8.1.xI would like to know if it is possible to return aresult of a select request and also a simple interger...that's definitelly possible (and by the way - is was possi ble years before mysql implemented it).
check the docs:http://www.postgresql.org/docs/8.1/interactive/plpgsql.htmldepesz-- 
http://www.depesz.com/ - nowy, lepszy depesz

[GENERAL] stored procedures

[Alain Roger]

Hi,i'm migrating some SP from MySQL to PostgreSQL 8.1.xI would like to know if it is possible to return aresult of a select request and also a simple interger...thanks a lot,Alain

Re: [GENERAL] Stored procedures..PLEASE HELP

[Alagu Madhu]

Hello,

  I am alagu madhu working as a Developer (postgresql).pls,send
your table design.






"Minal A. Aryamane" wrote:
> hello all,
> I desparately need help in developing stored procedure using postgresql 8.0. 
> I am using pgadmin for developing the stored procs. Can anyone please help 
> me, The docs are really not helpful.
>
>  am trying to pass values through  a jsp page wherein I am accessing this 
> stored procedures by the following select ststemet
>
> SELECT sp_build_course('ETLCRC0001:ETLCRC0010:', 'CRCSUB0001:CRCSUB0001:', 
> 'ABC', 'minal' )
>
> the function is as below:
>
> CREATE OR REPLACE FUNCTION sp_build_course("varchar", "varchar", "varchar", 
> "varchar")
>   RETURNS text AS
> '
> Declare
> COURSEIDS VARCHAR(1000) ;
> TOPICIDS VARCHAR(1000);
> COURSENAME VARCHAR(150);
> USERNAME VARCHAR(20);
>
> COURSEIDS1 VARCHAR(1000);
> TOPICIDS1 VARCHAR(1000);
> COURSEID1 VARCHAR(20);
> TOPICID1 VARCHAR(20);
> NEWCOURSEID VARCHAR(20);
> NEWTOPICID VARCHAR(20);
> COLUMN1 VARCHAR(50);
> COLUMN2 VARCHAR(50);
> COLUMN3 VARCHAR(50);
> COLUMN4 VARCHAR(50);
> QCODE1 VARCHAR(10);
> QDATE1 VARCHAR(10);
> QSNO INTEGER;
> WEIGHTAGE INTEGER;
> QSTYPE VARCHAR(50);
> QUESTION VARCHAR(1000);
> MARKS VARCHAR(50);
> QFLAG BOOL;
> PFLAG BIT;
> PAPER_TYPE VARCHAR(20);
> CINDEX INTEGER;
> TINDEX INTEGER;
> INDEX INTEGER;
> QINDEX INTEGER;
> QUESTIONID INTEGER;
> NEWQUESTIONID INTEGER;
> LESSON_FLOW_TITLE VARCHAR(50);
> TREE_NODE_TYPE VARCHAR(50);
> VIDEO_SIZE INTEGER;
> ROOT_NODE_ID INTEGER;
> SLIDE_ORDER INTEGER;
> row1 record;
> row2 record;
> row3 record;
> COURSENAME1 varchar(150);
> USERNAME1 varchar(50);
>
> BEGIN
> SET COURSEIDS1:=COURSEIDS;
> SET TOPICIDS1:=TOPICIDS;
> SET COURSENAME1:=COURSENAME;
> SET USERNAME1:=USERNAME;
>
> --PRINT \'INSIDE THE STORED PROCEDURE\'
>
> COLUMN1 := DATE_PART(\'Day\',current_date);
> COLUMN2 := DATE_PART(\'Month\',current_date);
> COLUMN3 := DATE_PART(\'Year\',current_date);
>
> IF LENGTH(COLUMN1) = 1 THEN
>  COLUMN1 := \'0\' || COLUMN1;
> END IF;
> IF LENGTH(COLUMN2) = 1 THEN
>  COLUMN2 := \'0\' || COLUMN2;
> END IF;
>
> QDATE1 := COLUMN1 || COLUMN2 || COLUMN3;
>
> SELECT trim(to_char(max(to_number(QCODE,\'99\'))+1,\'09\')) INTO 
> QCODE1 FROM QUESTION WHERE QDATE = QDATE1;
>
>  WHILE  (LENGTH(QCODE1) < 6) LOOP
>   QCODE1 := \'0\' || QCODE1;
>  END LOOP;
>
> SELECT MAX(SUBSTR(COURSEID,7,LENGTH(COURSEID))) INTO  NEWCOURSEID FROM 
> COURSEMASTER;
>
>  WHILE  (LENGTH(NEWCOURSEID) < 4) LOOP
>   NEWCOURSEID := \'0\' || NEWCOURSEID;
>  END LOOP;
>   NEWCOURSEID := \'ETLCRC\' || NEWCOURSEID;
>   INSERT INTO COURSEMASTER(COURSEID, COURSENAME) VALUES ( 
> NEWCOURSEID,COURSENAME1);
>   CINDEX := POSITION(\':\' IN COURSEIDS1);
>   TINDEX := POSITION(\':\' IN TOPICIDS1);
>   INDEX := 1;
>  WHILE (CINDEX >  0) LOOP
>   COURSEID1 := SUBSTR(COURSEIDS1,1,(CINDEX-1));
>   TOPICID := SUBSTR(TOPICIDS1,1,(TINDEX-1));
>
>  -- TRANSACTION STARTS HERE
>   NEWTOPICID := INDEX;
>WHILE  (LENGTH(NEWTOPICID) < 4) LOOP
> NEWTOPICID := \'0\' || NEWTOPICID;
>END LOOP;
>NEWTOPICID := \'CRCSUB\' || NEWTOPICID;
>INSERT INTO MYTABLE6 VALUES(COURSEID1, TOPICID, NEWCOURSEID, NEWTOPICID);
>INSERT INTO TOPICS SELECT NEWCOURSEID, NEWTOPICID, TOPICNAME  FROM TOPICS 
> WHERE COURSEID=COURSEID AND TOPICID=TOPICID;
>SELECT NULLIF(0,COUNT(*)) INTO COLUMN3 FROM LESSON_FLOW WHERE 
> COURSEID=COURSEID AND TOPICID=TOPICID;
>COLUMN1 := COURSEID1 || \'_\' || TOPICID || \'_\';
>COLUMN2 := NEWCOURSEID || \'_\' || NEWTOPICID || \'_\';
>IF (COLUMN3 >0) THEN
> SELECT MAX(ATTACHMENT_ID) + 1 INTO COLUMN3 FROM COURSE_LESSON;
> INSERT INTO COURSE_LESSON(COURSEID1, TOPICID, RECORD_STATUS, 
> ATTACHMENT_ID) VALUES (NEWCOURSEID, NEWTOPICID,0, CONVERT(INT, COLUMN3));
> INSERT INTO ATTACHMENT SELECT COLUMN3, COLUMN2 + 
> SUBSTR(URL,23,LENGTH(URL)), SEQUENCE FROM ATTACHMENT WHERE SUBSTR(URL,0,23) = 
> COLUMN1;
> SELECT MAX(LF_ID) + 1 INTO COLUMN4 FROM LESSON_FLOW;
>
> FOR row1 IN SELECT  LESSON_FLOW_TITLE, TREE_NODE_TYPE, VIDEO_SIZE, 
> ROOT_NODE_ID, SLIDE_ORDER FROM LESSON_FLOW WHERE COURSEID=COURSEID AND 
> TOPICID = TOPICID
> LOOP
>  LESSON_FLOW_TITLE:=row1.LESSON_FLOW_TITLE;
>  TREE_NODE_TYPE:=row1.TREE_NODE_TYPE;
>  VIDEO_SIZE:=row1.VIDEO_SIZE;
>  ROOT_NODE_ID:=row1.ROOT_NODE_ID;
>  SLIDE_ORDER:=row1.SLIDE_ORDER;
>
>  INSERT INTO LESSON_FLOW  VALUES (COLUMN4, NEWCOURSEID, NEWTOPICID, 
> LESSON_FLOW_TITLE, TREE_NODE_TYPE, VIDEO_SIZE, COLUMN3, ROOT_NODE_ID, 
> SLIDE_ORDER);
>  COLUMN4 := COLUMN4 + 1;
>
> END LOOP;
> INSERT INTO SELECTEDSESSION SELECT NEWCOURSEID, NEWTOPICID, COLUMN2 + 
> SUBSTR(URL,23,LENGTH(URL)) FROM SELECTEDSESSION WHERE COURSEID= COURSEID1 AND 
> TOPICID= TOPICID;
>END IF;
>QINDEX := 1;
>
>   FOR row2 IN SELECT QUESTION_ID, QSNO, WEIGHTAGE, QSTYPE, QUESTION, MARKS, 
> FLAG,  PAPER_TYPE FROM QUESTION WHERE COURSEID=COURSEID AND TOPICID = TOPICID
>   LOOP
>QUESTIONID:=row2.QUESTION_ID;
>   

Re: [GENERAL] Stored procedures..PLEASE HELP

[Richard Huxton]

Minal A. Aryamane wrote:

hello all,
I desparately need help in developing stored procedure using postgresql 8.0. I am using pgadmin for developing the stored procs. Can anyone please help me, The docs are really not helpful. 


 am trying to pass values through  a jsp page wherein I am accessing this 
stored procedures by the following select ststemet

SELECT sp_build_course('ETLCRC0001:ETLCRC0010:', 'CRCSUB0001:CRCSUB0001:', 
'ABC', 'minal' )

the function is as below:

CREATE OR REPLACE FUNCTION sp_build_course("varchar", "varchar", "varchar", 
"varchar")
  RETURNS text AS

[snip]

SET COURSEIDS1:=COURSEIDS;
SET TOPICIDS1:=TOPICIDS;
SET COURSENAME1:=COURSENAME;
SET USERNAME1:=USERNAME;


Umm - where do you get "SET" from? It's not in any of the manuals.


--PRINT \'INSIDE THE STORED PROCEDURE\'


Or for that matter "PRINT"
[snip]


when I run it I get the following error:
ERROR:  syntax error at or near "$1" at character 6
CONTEXT:  PL/pgSQL function "sp_build_course" line 45 at SQL statement.


From psql I get the following...

richardh=> \i fn_syntax_err.sql
CREATE FUNCTION
richardh=>
richardh=> SELECT sp_build_course('a','b','c','d');
ERROR:  syntax error at or near "$1" at character 6
QUERY:  SET  $1 := $2
CONTEXT:  PL/pgSQL function "sp_build_course" line 45 at SQL statement
LINE 1: SET  $1 := $2
 ^

Looks to me like it's the SET it doesn't like.
--
  Richard Huxton
  Archonet Ltd

---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings

[GENERAL] Stored procedures..PLEASE HELP

[Minal A. Aryamane]

hello all,
I desparately need help in developing stored 
procedure using postgresql 8.0. I am using pgadmin for developing the stored 
procs. Can anyone please help me, The docs are really not helpful. 
 
 am trying to pass values through  a jsp 
page wherein I am accessing this stored procedures by the following select 
ststemet
 
SELECT sp_build_course('ETLCRC0001:ETLCRC0010:', 
'CRCSUB0001:CRCSUB0001:', 'ABC', 'minal' )
 
the function is as below:
 
CREATE OR REPLACE FUNCTION 
sp_build_course("varchar", "varchar", "varchar", "varchar")  RETURNS 
text AS'Declare COURSEIDS VARCHAR(1000) ;TOPICIDS 
VARCHAR(1000);COURSENAME VARCHAR(150);USERNAME VARCHAR(20);
 
COURSEIDS1 VARCHAR(1000); TOPICIDS1 
VARCHAR(1000); COURSEID1 VARCHAR(20);TOPICID1 
VARCHAR(20);NEWCOURSEID VARCHAR(20);NEWTOPICID VARCHAR(20);COLUMN1 
VARCHAR(50);COLUMN2 VARCHAR(50);COLUMN3 VARCHAR(50);COLUMN4 
VARCHAR(50);QCODE1 VARCHAR(10);QDATE1 VARCHAR(10);QSNO 
INTEGER;WEIGHTAGE INTEGER;QSTYPE VARCHAR(50);QUESTION 
VARCHAR(1000);MARKS VARCHAR(50);QFLAG BOOL;PFLAG BIT;PAPER_TYPE 
VARCHAR(20);CINDEX INTEGER;TINDEX INTEGER;INDEX INTEGER;QINDEX 
INTEGER;QUESTIONID INTEGER;NEWQUESTIONID INTEGER;LESSON_FLOW_TITLE 
VARCHAR(50);TREE_NODE_TYPE VARCHAR(50);VIDEO_SIZE 
INTEGER;ROOT_NODE_ID INTEGER;SLIDE_ORDER INTEGER;row1 
record;row2 record;row3 record;COURSENAME1 
varchar(150);USERNAME1 varchar(50);
 
BEGINSET COURSEIDS1:=COURSEIDS;SET 
TOPICIDS1:=TOPICIDS;SET COURSENAME1:=COURSENAME;SET 
USERNAME1:=USERNAME;
 
--PRINT \'INSIDE THE STORED 
PROCEDURE\'
 
COLUMN1 := 
DATE_PART(\'Day\',current_date);COLUMN2 := 
DATE_PART(\'Month\',current_date);COLUMN3 := 
DATE_PART(\'Year\',current_date);
 
IF LENGTH(COLUMN1) = 1 THEN COLUMN1 := 
\'0\' || COLUMN1;END IF;IF LENGTH(COLUMN2) = 1 THEN COLUMN2 := 
\'0\' || COLUMN2;END IF;
 
QDATE1 := COLUMN1 || COLUMN2 || 
COLUMN3;
 
SELECT 
trim(to_char(max(to_number(QCODE,\'99\'))+1,\'09\')) INTO QCODE1 FROM 
QUESTION WHERE QDATE = QDATE1;
 
 WHILE  (LENGTH(QCODE1) < 6) 
LOOP  QCODE1 := \'0\' || QCODE1; END LOOP;
 
SELECT MAX(SUBSTR(COURSEID,7,LENGTH(COURSEID))) 
INTO  NEWCOURSEID FROM COURSEMASTER;
 
 WHILE  (LENGTH(NEWCOURSEID) < 4) 
LOOP  NEWCOURSEID := \'0\' || NEWCOURSEID; END 
LOOP;  NEWCOURSEID := \'ETLCRC\' || 
NEWCOURSEID;  INSERT INTO COURSEMASTER(COURSEID, COURSENAME) 
VALUES ( NEWCOURSEID,COURSENAME1);  CINDEX := POSITION(\':\' IN 
COURSEIDS1);  TINDEX := POSITION(\':\' IN 
TOPICIDS1);  INDEX := 1; WHILE (CINDEX >  0) 
LOOP  COURSEID1 := 
SUBSTR(COURSEIDS1,1,(CINDEX-1));  TOPICID := 
SUBSTR(TOPICIDS1,1,(TINDEX-1));
 
 -- TRANSACTION STARTS 
HERE  NEWTOPICID := INDEX;   WHILE  
(LENGTH(NEWTOPICID) < 4) LOOPNEWTOPICID := \'0\' 
|| NEWTOPICID;   END LOOP;   NEWTOPICID := 
\'CRCSUB\' || NEWTOPICID;   INSERT INTO MYTABLE6 
VALUES(COURSEID1, TOPICID, NEWCOURSEID, NEWTOPICID);   INSERT 
INTO TOPICS SELECT NEWCOURSEID, NEWTOPICID, TOPICNAME  FROM TOPICS WHERE 
COURSEID=COURSEID AND TOPICID=TOPICID;   SELECT 
NULLIF(0,COUNT(*)) INTO COLUMN3 FROM LESSON_FLOW WHERE COURSEID=COURSEID AND 
TOPICID=TOPICID;   COLUMN1 := COURSEID1 || \'_\' || TOPICID 
|| \'_\';   COLUMN2 := NEWCOURSEID || \'_\' || NEWTOPICID || 
\'_\';   IF (COLUMN3 >0) 
THENSELECT MAX(ATTACHMENT_ID) + 1 INTO COLUMN3 FROM 
COURSE_LESSON;INSERT INTO COURSE_LESSON(COURSEID1, 
TOPICID, RECORD_STATUS, ATTACHMENT_ID) VALUES (NEWCOURSEID, NEWTOPICID,0, 
CONVERT(INT, COLUMN3));INSERT INTO ATTACHMENT SELECT 
COLUMN3, COLUMN2 + SUBSTR(URL,23,LENGTH(URL)), SEQUENCE FROM ATTACHMENT WHERE 
SUBSTR(URL,0,23) = COLUMN1;SELECT MAX(LF_ID) + 1 
INTO COLUMN4 FROM LESSON_FLOW;
 
FOR row1 IN SELECT  
LESSON_FLOW_TITLE, TREE_NODE_TYPE, VIDEO_SIZE, ROOT_NODE_ID, SLIDE_ORDER FROM 
LESSON_FLOW WHERE COURSEID=COURSEID AND TOPICID = 
TOPICIDLOOP LESSON_FLOW_TITLE:=row1.LESSON_FLOW_TITLE; TREE_NODE_TYPE:=row1.TREE_NODE_TYPE; VIDEO_SIZE:=row1.VIDEO_SIZE; ROOT_NODE_ID:=row1.ROOT_NODE_ID; SLIDE_ORDER:=row1.SLIDE_ORDER;
 
 INSERT INTO 
LESSON_FLOW  VALUES (COLUMN4, NEWCOURSEID, NEWTOPICID, LESSON_FLOW_TITLE, 
TREE_NODE_TYPE, VIDEO_SIZE, COLUMN3, ROOT_NODE_ID, 
SLIDE_ORDER); COLUMN4 := COLUMN4 + 
1; END 
LOOP;INSERT INTO SELECTEDSESSION SELECT NEWCOURSEID, 
NEWTOPICID, COLUMN2 + SUBSTR(URL,23,LENGTH(URL)) FROM SELECTEDSESSION WHERE 
COURSEID= COURSEID1 AND TOPICID= TOPICID;   END 
IF;   QINDEX := 1; FOR 
row2 IN SELECT QUESTION_ID, QSNO, WEIGHTAGE, QSTYPE, QUESTION, MARKS, 
FLAG,  PAPER_TYPE FROM QUESTION WHERE COURSEID=COURSEID AND TOPICID = 
TOPICID  LOOP   QUESTIONID:=row2.QUESTION_ID;   QSNO:=row2.QSNO;   WEIGHTAGE:=row2.WEIGHTAGE;   QSTYPE:=row2.QSTYPE;   QUESTION:=row2.QUESTION;   MARKS:=row2.MARKS;   QFLAG:=row2.FLAG;   PAPER_TYPE:=row2.PAPER_TYPE;  INSERT 
INTO QUESTION (COURSEID1, TOPICID, QSNO, WEIGHTAGE, QSTYPE, QUESTION, MARKS, 
QDATE, QCODE,FLAG, USERNAME, PAPER_TYPE) VALUES (NEWCOURSEID, NEWTOPICID, 
QINDEX, WEIGHTAGE, QSTYPE, QUESTION, MARKS, QDATE1, QCODE,QFLAG, USERNAME1, 
PAPER_TYPE);   NEW

[GENERAL] Stored Procedures for Security

[Alex Stapleton]

What is everyone's opinion for exclusively using Stored Procedures  
for increased security? The scenario I am thinking of means having  
one user which is widely accessible (large numbers of staff/user for  
example) and only has access to some stored procedures, which where  
create with SECURITY DEFINER so that they are run with the definers  
permissions (which are assumed liberal enough to do whatever is  
needed of the stored procedures.) I am especially wondering about how  
easy it would be to write the functions in a way which made it very  
hard/impossible to do something that wasn't intended of them. Has  
anyone implemented anything like this before? What was developing new  
bits of code which required access to it like?


---(end of broadcast)---
TIP 6: explain analyze is your friend

Re: [GENERAL] stored procedures / C-Language

[Magnus Hagander]

Title: stored procedures / C-Language



 

  
  hi, 
  i'm trying to find some documentation about writing stored 
  procedures with C-language. i found description in 
  postgresql's documentation but i don't understand everything. 
  Aat present, i have a pl/perl stored procedure but it isn't 
  fast enough, so somebody recommend me to write it using c-language.
  i don't know how to create this, and how asking the database, 
  recovering data and managing them. 
  For the moment, my application is running on Windows 2000 NT, 
  is it possible to write stored procedures using C-language ? may i write them 
  using Visual C++ or does i need to use ANSI compiler ?
  does this code will run with Linux or Solaris ? 
  sorry for all these questions, but i don't how it works, and 
  my application must works at the end of the week :-( 
  thanks a lots 
 At the moment you muse use MingW (http://www.mingw.org) to compile your 
extensions. Assuming you don't use any platform specific code, it should run 
fine on Linux or Solaris with a simlpe recompile (the postgresql interfaces are 
the same).
(Visual C++ is an ANSI compliant compiler, but 
that won't help you here..)
//Magnus 
 

[GENERAL] stored procedures / C-Language

[FERREIRA William (COFRAMI)]

Title: stored procedures / C-Language





hi,


i'm trying to find some documentation about writing stored procedures with C-language.
i found description in postgresql's documentation but i don't understand everything.


Aat present, i have a pl/perl stored procedure but it isn't fast enough, so somebody recommend me to write it using c-language.

i don't know how to create this, and how asking the database, recovering data and managing them.


For the moment, my application is running on Windows 2000 NT, is it possible to write stored procedures using C-language ? may i write them using Visual C++ or does i need to use ANSI compiler ?

does this code will run with Linux or Solaris ?


sorry for all these questions, but i don't how it works, and my application must works at the end of the week :-(


thanks a lots


    Will

Re: [GENERAL] Stored Procedures

[Alvaro Herrera]

On Tue, Sep 21, 2004 at 11:11:33AM -0700, Chris Travers wrote:
> Alvaro Herrera wrote:
> 
> >There's only one transaction (whether it's an explicit transaction block
> >or an implicit one), and the query that invokes the stored procedure is
> >already running inside it.  So the stored procedure always has the
> >safety of it, and it can't get out (except by raising an error and
> >aborting the whole thing).  The transaction can only be committed
> >_after_ the stored procedure has finished succesfully.
>
> I am assuming that save points would still work as advertised in stored 
> procedures

Not at all.  What you actually use is exception blocks.

-- 
Alvaro Herrera ()
"El sentido de las cosas no viene de las cosas, sino de
las inteligencias que las aplican a sus problemas diarios
en busca del progreso." (Ernesto Hernández-Novich)


---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster

Re: [GENERAL] Stored Procedures

[Alvaro Herrera]

On Tue, Sep 21, 2004 at 07:41:31AM -0400, Kent Anderson wrote:
> We are currently switching to stored procedures for a lot of our database
> activity. The question has come up about the transactional nature of the
> stored procedures.  I was wondering if stored procedures can have
> transactions in them or if you must start the transaction in your code and
> call the stored procedure from there to get the safety of a transaction?

There's only one transaction (whether it's an explicit transaction block
or an implicit one), and the query that invokes the stored procedure is
already running inside it.  So the stored procedure always has the
safety of it, and it can't get out (except by raising an error and
aborting the whole thing).  The transaction can only be committed
_after_ the stored procedure has finished succesfully.

-- 
Alvaro Herrera ()
Jajaja! Solo hablaba en serio!


---(end of broadcast)---
TIP 7: don't forget to increase your free space map settings

[GENERAL] Stored Procedures

[Kent Anderson]

We are currently 
switching to stored procedures for a lot of our database activity. The question 
has come up about the transactional nature of the stored procedures.  I was 
wondering if stored procedures can have transactions in them or if you must start the 
transaction in your code and call the stored procedure from there to get the 
safety of a transaction?
 
Thanks
Kent Anderson
EZYield.com
407-629-0900
www.ezyield.com
 

This electronic message transmission contains 
information from the Company that may be proprietary, confidential and/or 
privileged. The information is intended only for the use of the individual(s) or 
entity named above.  If you are not the intended recipient, be aware that 
any disclosure, copying or distribution or use of the contents of this 
information is prohibited.  If you have received this electronic 
transmission in error, please notify the sender immediately by replying to the 
address listed in the "From:" field.
 

Re: [GENERAL] Stored Procedures woes

[Jochem van Dieten]

"Andrew Hall" wrote:
We are using a number of stored procedures that are called often from our
client programs. I include one here as an example. The problem we are seeing
is that when executing some of these that deal with a large number of
records, they begin execution and never return. The process handling the
request chews 97% of CPU resources and must be cancelled before it will
release. We have tried examining where in the process it stops via the debug
output, but this has proven to be fruitless as A. the problem doesn't happen
with all the SP's (some other SP's deal with far more data but don't have
the problem), and B. doesn't always happen consistently with the SP's that
seem to cause problems. What we do know is that the machine doesn't seem to
be low on memory, never returns any error, and happens regardless of machine
or PG version (we've tried 7.4.1, and 7.4.2).
Wouldn't the following query be functionally the same as the 
procedure you posted (if you fix the rate and the groupid)? If 
so, does it perform better and how does the explain look?

UPDATE user_sessions
SET cost_bytes_in = a.costIn,
cost_bytes_out = a.costOut
FROM (
  SELECT
session_id,
CASE
  WHEN 
(us_rec.hit_bytes_in+us_rec.miss_bytes_in)*$rate/1048576 < 0.0001 
THEN 0
  ELSE 
(us_rec.hit_bytes_in+us_rec.miss_bytes_in)*$rate/1048576 END
AS costIn,
CASE
  WHEN 
(us_rec.hit_bytes_out+us_rec.miss_bytes_out)*$rate/1048576 < 
0.0001 THEN 0
  ELSE 
(us_rec.hit_bytes_out+us_rec.miss_bytes_out)*$rate/1048576 END
AS costOut
  FROM user_session
  WHERE group_id = $groupId
) a
WHERE group_id = $groupId AND user_id = a.user_id;

Jochem
---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster

Re: [GENERAL] Stored Procedures woes

[Tom Lane]

Oliver Elphick <[EMAIL PROTECTED]> writes:
>> We are using a number of stored procedures that are called often from our
>> client programs.

> Are you somehow setting off an infinite recursion?  How is this being
> called?

I doubt it would be an infinite-recursion problem, as that would soon
lead to stack overflow and core dump (or at least very obvious memory
bloat).  I'm wondering about inefficient plans being generated for the
queries in the procedure.

> Try putting RAISE NOTICE statements in to track the progress of the
> code.

Right.  See also recent discussion on pgsql-performance about
investigating plans used for plpgsql queries.

regards, tom lane

---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]

Re: [GENERAL] Stored Procedures woes

[Oliver Elphick]

On Thu, 2004-08-19 at 10:53, Andrew Hall wrote:
> Hello,
> 
> We are using a number of stored procedures that are called often from our
> client programs. I include one here as an example. The problem we are seeing
> is that when executing some of these that deal with a large number of
> records, they begin execution and never return.
...
> 
> CREATE OR REPLACE FUNCTION updateUserSessionCost(INTEGER,DOUBLE precision)
> RETURNS INTEGER AS '
>  DECLARE
...
>  BEGIN
>   -- get all user belong to that group
>   FOR user_rec IN SELECT distinct user_id FROM user_session where
> group_id=groupId LOOP
>   userId = user_rec.user_id;
>   -- loop all session record for that user
>   FOR us_rec IN SELECT
> session_id,hit_bytes_in,miss_bytes_in,hit_bytes_out,miss_bytes_out FROM
> user_session where user_id=userId and group_id=groupId LOOP
...
>   update user_session set cost_bytes_in=costIn,
>   cost_bytes_out=costOut WHERE user_id=userId AND
> session_id=us_rec.session_id;

Are you somehow setting off an infinite recursion?  How is this being
called?

Try putting RAISE NOTICE statements in to track the progress of the
code.


Oliver Elphick


---(end of broadcast)---
TIP 3: if posting/reading through Usenet, please send an appropriate
  subscribe-nomail command to [EMAIL PROTECTED] so that your
  message can get through to the mailing list cleanly

[GENERAL] Stored Procedures woes

[Andrew Hall]

Hello,

We are using a number of stored procedures that are called often from our
client programs. I include one here as an example. The problem we are seeing
is that when executing some of these that deal with a large number of
records, they begin execution and never return. The process handling the
request chews 97% of CPU resources and must be cancelled before it will
release. We have tried examining where in the process it stops via the debug
output, but this has proven to be fruitless as A. the problem doesn't happen
with all the SP's (some other SP's deal with far more data but don't have
the problem), and B. doesn't always happen consistently with the SP's that
seem to cause problems. What we do know is that the machine doesn't seem to
be low on memory, never returns any error, and happens regardless of machine
or PG version (we've tried 7.4.1, and 7.4.2).

We must be doing something fundamentally wrong , so if anyone can shed any
light on why this might be happening and how I can track it down, would be
great. We don't have a whole lot of experience with PG, so there may very
likely be something we're doing wrong.

Thanks,

Andrew.

CREATE OR REPLACE FUNCTION updateUserSessionCost(INTEGER,DOUBLE precision)
RETURNS INTEGER AS '
 DECLARE
  groupId ALIAS FOR $1;
  rate ALIAS FOR $2;
  user_rec RECORD;
  us_rec RECORD;
  userId text;
  costIn DOUBLE precision;
  costOut DOUBLE precision;
 BEGIN
  -- get all user belong to that group
  FOR user_rec IN SELECT distinct user_id FROM user_session where
group_id=groupId LOOP
  userId = user_rec.user_id;
  -- loop all session record for that user
  FOR us_rec IN SELECT
session_id,hit_bytes_in,miss_bytes_in,hit_bytes_out,miss_bytes_out FROM
user_session where user_id=userId and group_id=groupId LOOP
  costIn :=
(us_rec.hit_bytes_in+us_rec.miss_bytes_in)*rate/1048576;
  costOut :=
(us_rec.hit_bytes_out+us_rec.miss_bytes_out)*rate/1048576;
  if (costIn < 0.0001) then
  costIn := 0;
  end if;
  if (costOut < 0.0001) then
  costOut := 0;
  end if;

  update user_session set cost_bytes_in=costIn,
  cost_bytes_out=costOut WHERE user_id=userId AND
session_id=us_rec.session_id;

  END LOOP;
  END LOOP;
  return 1;
 END;
 ' LANGUAGE 'plpgsql'
;


---(end of broadcast)---
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])

[GENERAL] Stored procedures

[Secrétariat]

Hello !
My ODBC driver in MS Visual Basic 6 refers also to 
"Stored procedures" as Record source.
What are these stored procedures, I don't find 
anything in the 7.4 documentation,
Where can I find documentation ?
Many thanks.
Luc

Re: [GENERAL] Stored procedures output variables

[Bill Moran]

Shachar Shemesh wrote:
Hi List,

How do I define a stored procedure that has an output variable? I am 
looking for a way that will not significantly change the way the 
argument is called.

Can I, for example, pass a reference to variable in some way?
If I understand your question, the best way is probably to define a type.

I assume you are intending to return multiple variables, since a single
variable is handled easily ... for example, to return an int:
CREATE FUNCITON returns_int()
RETURNS INT
AS '
 DECLARE
 BEGIN
  RETURN 5;
 END;
' LANGUAGE 'plpgsql';
If you want to return multiple variables, there are two different techniques.
1) If you want to return many of the same type of variable, return a set:
CREATE FUNCTION returns_many_int( ... parameters ...)
RETURNS SETOF INT
AS '
...
This will return as many INTs as you need.  To the function/command that called
the function, it will look as if a table has been returned i.e. for a SELECT,
you'd do:
SELECT * FROM returns_many_int(... parameters ...);
2) If you want to return different types of values, you'll probably want to
   define a custom rowtype:
CREATE TYPE custom_row AS (
 col1 INT,
 col2 TEXT,
 col3 BOOL
);
CREATE FUNCTION returns_custom_type(... parameters ...)
RETURNS custom_row
AS '
...
The result will look like a single row from a table.

You can combine the two, for example if you need to return multiple custom_row's

See the docs on this for more details and much better explanation that I can make:
http://www.postgresql.org/docs/7.4/static/plpgsql.html
Hope this helps.

--
Bill Moran
Potential Technologies
http://www.potentialtech.com
---(end of broadcast)---
TIP 7: don't forget to increase your free space map settings

[GENERAL] Stored procedures output variables

[Shachar Shemesh]

Hi List,

How do I define a stored procedure that has an output variable? I am 
looking for a way that will not significantly change the way the 
argument is called.

Can I, for example, pass a reference to variable in some way?

Shachar

--
Shachar Shemesh
Lingnu Open Source Consulting
http://www.lingnu.com/
---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?
  http://www.postgresql.org/docs/faqs/FAQ.html

Re: [GENERAL] Stored procedures from MS Query

[Shridhar Daithankar]

On Tuesday 28 October 2003 16:06, Tomas Larsson wrote:
> Hi!
>
> We're trying to call a set-returning stored procedure
> from Excel (MS Query). To do this we type:
>
> select * from getStudents()
>
> (where getStudents() is the stored procedure).
> The problem is that MS Query interprets this as if
> getStudents was a table, and complains that there is
> no table called getStudents(). How do we get around
> this?

Create a dummy table and a rule that  calls this function instead of actually 
selecting the from the table.

HTH

 Shridhar


---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]

Re: [GENERAL] Stored procedures from MS Query

[Tomas Larsson]

Thanks Shridhar!

Works like a charm!

/tomas, Patrik and Siv.

--- Shridhar Daithankar
<[EMAIL PROTECTED]> wrote:
> On Tuesday 28 October 2003 16:06, Tomas Larsson
> wrote:
> > Hi!
> >
> > We're trying to call a set-returning stored
> procedure
> > from Excel (MS Query). 

> Create a dummy table and a rule that  calls this
> function instead of actually 
> selecting the from the table.
> 
> HTH
> 
>  Shridhar


__
Do you Yahoo!?
Exclusive Video Premiere - Britney Spears
http://launch.yahoo.com/promos/britneyspears/

---(end of broadcast)---
TIP 8: explain analyze is your friend

[GENERAL] Stored procedures from MS Query

[Tomas Larsson]

Hi!

We're trying to call a set-returning stored procedure
from Excel (MS Query). To do this we type:

select * from getStudents()

(where getStudents() is the stored procedure).
The problem is that MS Query interprets this as if
getStudents was a table, and complains that there is
no table called getStudents(). How do we get around
this?

We would be very grateful for any help!

/tomas, Patrik and Siv.


__
Do you Yahoo!?
Exclusive Video Premiere - Britney Spears
http://launch.yahoo.com/promos/britneyspears/

---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster

[GENERAL] Stored procedures with variable number of args ?

[Bruno BAGUETTE]

Hello,

Is it possible to make PL/PGSQL stored procedures with a variable number
of args ?

If yes, is it possible to have a sample ?

Thanks you in advance :-)

---
Bruno BAGUETTE - [EMAIL PROTECTED] 


---(end of broadcast)---
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])

[GENERAL] stored procedures

[Alexander Lohse]

Hi,

I am very new to PgSQL and I am just trying to figure out what for to 
use stored procedures.
What I read/tested up to now is that pl/pgsql seems to be a bit 
clumsy to write. Possibly needing a lot of
trial & error.

So, I am not really sure what is the benefit of writing logic inside the DB.
Is there a performance benefit compared to processing via PHP?

I do not really see the use for me to separate logic from the other logic,
'cause then you'll have to worry about 2 sources!
(I usually have a DB only used by that specific application, and if 
not it is at least the same language, so global function are stored 
in shared code libraries.)

I see this is a powerful feature and it seems to make sense, but when 
I scan across my apps, currently using MySQL, I am having a hard time 
to imagine where I could use it.
As far as I figured now, the only thing I would put into DB-Logic 
would be stuff like "deleting or updating related tables". But this 
is what REFERENCES  CASCADING does as well!

It would help me lot, if anyone could help me here to jump on that train.
Maybe one can tell me what he does with this opporunity.

Thank you very much in advance,

Alex
-- 
___
Human Touch Medienproduktion GmbH
Am See 1
17440 Klein Jasedow

Alexander Lohse
Tel: (038374) 75211
Fax: (038374) 75223
eMail: [EMAIL PROTECTED]
http://www.humantouch.de

---(end of broadcast)---
TIP 6: Have you searched our list archives?

http://www.postgresql.org/search.mpl

[GENERAL] Stored Procedures

[Andreas Tille]

Hello

after playing around for a while with PostgreSQL's Stored Functions
I think I understood the concept behind it a little bit.  It is mighty
but quite different from all other SQL servers Stored Procedures.
Considering the fact that there is another OpenSourced SQL database
engine Interbase, which knows Stored Procedures, I wonder if there
would be a way to port this stuff to one of the next releases of
PostgreSQL.  This would really simplify the porting efforts and so
PostgreSQL could gain more popularity because its easier for people
to switch.  The main problem is that there is a need to return a
table (several data columns) from a Stored Procedure which is not
possible with Stored Functions.

Just an idea

  Andreas.

[GENERAL] Stored procedures returning multiple values... ?

[Henrique Pantarotto]

Hello friends,

many months ago I starting messing with stored procedures with PostgreSQL,
and at the time, I found out that I could only return "one" value when I
used a stored procedure.

I was wondering if this has changed or has/will/could be changed/implemented
with PostgreSQL 7.0?

It would be fun if stored procedured could return multiple values, wouldn't
it?  ;-)


Thanks,
___
Henrique Pantarotto
Administrador de Sistemas - SITE São Paulo
Terra Networks Brasil S/A
A Internet do Brasil agora é TERRA
Tel: (11) 5505-5728 r.214/260/238
ICQ: 6934285
[EMAIL PROTECTED]

[GENERAL] stored procedures

[Matthew Hixson]

Could someone point me to a tutorial or HOWTO regarding stored procedures?  I
know very little about them and need to learn everything I possibly can about
them as soon as possible.  I looked throught the Postgres docs and couldn't
find anything that goes into them in depth.
   Thank you,
   -M@

--
Matthew Hixson - CIO 
FroZenWave Communications 
http://www.frozenwave.com

Re: [GENERAL] Stored Procedures

[Brett W. McCoy]

On Fri, 5 Nov 1999, Jeff MacDonald wrote:

>   1: does postgres support stored procedures

Yes, quite extensively, and far beyond what SQL 7 offers.  You may want 
to take a look at the programmer's manual for the documentation.

>   2: say a user has a microsoft sql server 7 database
>   with ~120 stored procedures, and alot of data, is their
>   a script or tool to convert that to a postgres database
>   or does it have to be done by hand.
> 

You can convert the data over with ODBC, but not the stored procedures. 
Under SQL 7, stored procedures are essentially SQL batch files and cannot
be used as a term in an expression, whereas under PostgreSQL, you can
create true functions in PL/PgSQL (a procedural language akin to
Oracle's), or as loadable executable modules written in C, C++, Tcl, etc.,
that returns values and objects, and can be used in an expression. 

I was shocked recently when we put in SQL 7 in our office (we've been 
using PostgreSQL for a while now, but we needed SQL 7 to use with a 
commercial retrieval system) and needed to start writing functions as I 
was used to under PostgreSQL, and couldn't.  A big win for PostgreSQL!

Brett W. McCoy   
 http://www.lan2wan.com/~bmccoy
---
There are people so addicted to exaggeration that they can't tell the
truth without lying.

[GENERAL] Stored Procedures

[Jeff MacDonald]

Greets,

Couple of questions, 

1: does postgres support stored procedures
2: say a user has a microsoft sql server 7 database
with ~120 stored procedures, and alot of data, is their
a script or tool to convert that to a postgres database
or does it have to be done by hand.


Jeff MacDonald
[EMAIL PROTECTED]

===
 So long as the Universe had a beginning, we can suppose it had a 
creator, but if the Universe is completly self contained , having 
no boundry or edge, it would neither be created nor destroyed
 It would simply be.
===