[SQL] Looking for examples of S/P
In order to learn SQL-Stored Procedure techniqes I'm looking for a series of examples. Where can I find examples of SQL and PL/pgSQL based stored procedures? Or any of you who wants to donate some? ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [SQL] Looking for examples of S/P
On Wed, Jan 19, 2005 at 10:03:13AM +0100, KÖPFERL Robert wrote: > In order to learn SQL-Stored Procedure techniqes I'm looking for a series of > examples. > Where can I find examples of SQL and PL/pgSQL based stored procedures? The General Bits column would be one place: http://www.varlena.com/varlena/GeneralBits/ -- Michael Fuhr http://www.fuhr.org/~mfuhr/ ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [SQL] Returning a bool on DELETE in a proc.
Thanks, but that was not what I was looking for. Seems that I forgot to mention that this function is intended to be written in SQL, not in PL/SQL. For the second one I already picked it off the fascinating* doc (as Keith did) that GET DIAGONSTIC is the right thing. So what variable/function is the correct SQL-equivalent to ROW_COUNT and can it be used in the following statement ? like DELETE...; SELECT (ROW_COUNT<>0); to return a bool value? Or would you really suggest to change the fct's type to void? *The doc is fascinating, because it is rather short in terms of PL/pgSQL but if you are looking for something you can find it in some example or mentioned in a sentence somweheewere > -Original Message- > From: Michael Fuhr [mailto:[EMAIL PROTECTED] > Sent: Dienstag, 18. Jänner 2005 19:52 > To: KÖPFERL Robert > Cc: pgsql-sql@postgresql.org > Subject: Re: [SQL] Returning a bool on DELETE in a proc. > > > On Tue, Jan 18, 2005 at 05:04:58PM +0100, KÖPFERL Robert wrote: > > > I'm currently writing a function which encapsulates a > delete and should > > return a bool as indicator for success. > > How do you define success? When the delete affects one or more rows? > > > DELETE FROM "TariffDetails" WHERE "TariffId"=$1 and > "BNumberPrefix"=$2; > > SELECT TRUE; > > > > but this makes me not happy. > > How can I distingruish wehter DELETE affected 0 or more > rows and return that > > while DELETE is not capable of returning any value? > > If you're using PL/pgSQL then see "Obtaining the Result Status" in > the "Basic Statements" section of the PL/pgSQL documentation. > > > And is the whole function executed if the DELETE fails or > will it stop > > bevore the select? > > A PL/pgSQL function should exit immediately with an error if the > DELETE fails. But understand that PL/pgSQL's notion of a failure > might not match yours: trying to delete from a non-existent table > is a failure, but deleting zero rows from an existing table isn't. > > -- > Michael Fuhr > http://www.fuhr.org/~mfuhr/ > ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] mail + rfc822, rfc2822 + schema
O Ron Peterson έγραψε στις Jan 18, 2005 : > On Tue, Jan 18, 2005 at 05:05:55PM +0200, Achilleus Mantzios wrote: > > > i am thinking of doing a remote MUA web-based system, based on > > postgresql. > > ... > > So, i'd like to know if any of you has designed a schema serving as an > > mail storage. If anything of caution arrose to you, and if you had > > something to recommend. > > You might want to look at dbmail. > http://www.dbmail.org/index.php?page=overview Thanx, i also found http://yukatan.sourceforge.net/ which seems pretty intuitive and well designed. > > -- -Achilleus ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] Returning a bool on DELETE in a proc.
KÖPFERL Robert wrote: Thanks, but that was not what I was looking for. Seems that I forgot to mention that this function is intended to be written in SQL, not in PL/SQL. For the second one I already picked it off the fascinating* doc (as Keith did) that GET DIAGONSTIC is the right thing. So what variable/function is the correct SQL-equivalent to ROW_COUNT and can it be used in the following statement ? like DELETE...; SELECT (ROW_COUNT<>0); to return a bool value? SQL doesn't support that (although I suppose it could be made to with some pg_rows_affected() function). You'll need to use plpgsql (or similar) if you want a result status returned. -- Richard Huxton Archonet Ltd ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [SQL] Looking for examples of S/P
I use this stored procedure to insert data into tables from my web page.
I call it using
select insert_masteraccount($1,$,2,$3,$4,$5,$6,$7,$8,$9,$10,$11,$12,$13);
CREATE OR REPLACE FUNCTION insert_masteraccount("varchar", "varchar",
"varchar", "varchar", "varchar", "varchar", "varchar", "varchar", "varchar",
"varchar", "varchar", "varchar", "varchar")
RETURNS "varchar" AS
'
DECLARE
dhcpvarchar:=\'DHCP\';
rtype varchar:=\'RAS\';
passvarchar:=\'Password\';
poolvarchar:=\'Pool-Name\';
class varchar:=\'Class\';
ip varchar:=\'Framed-IP-Address\';
BEGIN
insert into masteraccount(fname,midint,lname,username,atype)
values($1,$2,$3,$4,$5);
insert into passwd(u_id,currentpwd) values((select max(u_id) from
masteraccount where username=$4),$6);
insert into
ipinfo(u_id,ipaddress,atype,phone_num,billing,groupname,poolname)
values((select max(u_id) from masteraccount where
username=$4),$7,$5,$10,$11,$12,$13);
insert into userinfo(u_id,agency,user_email) values((select max(u_id) from
masteraccount where username=$4),$8,$9);
insert into radcheck(username,attribute,value) values($4,pass,$6);
if $7 != dhcp then
insert into radreply(username,attribute,value)
values($4,ip,$7);
else
insert into radcheck(username,attribute,value)
values($4,pool,$13);
end if;
return masteraccount.username where masteraccount.username=$4;
END;
'
LANGUAGE 'plpgsql' VOLATILE;
This is one that watches my ipinfo table and move data around for me and is
setup with as trigger function
CREATE OR REPLACE FUNCTION ipinfo_trg()
RETURNS "trigger" AS
'DECLARE
dhcpvarchar:=\'DHCP\';
rtype varchar:=\'RAS\';
poolvarchar:=\'Pool-Name\';
BEGIN
if NEW.ipaddress != dhcp then
if OLD.ipaddress != dhcp then
if OLD.atype != rtype then
insert into vpnip(ipaddress)
values(inet(OLD.ipaddress));
else
insert into rasip(ipaddress)
values(inet(OLD.ipaddress));
end if;
else end if;
else
if OLD.ipaddress != dhcp then
if OLD.atype != rtype then
insert into vpnip(ipaddress)
values(inet(OLD.ipaddress));
else
insert into rasip(ipaddress)
values(inet(OLD.ipaddress));
end if;
else end if;
END IF;
Return NEW;
END;
'
LANGUAGE 'plpgsql' VOLATILE;
-Original Message-
From: KÖPFERL Robert [mailto:[EMAIL PROTECTED]
Sent: Wednesday, January 19, 2005 4:03 AM
To: pgsql-sql@postgresql.org
Subject: [SQL] Looking for examples of S/P
In order to learn SQL-Stored Procedure techniqes I'm looking for a series of
examples.
Where can I find examples of SQL and PL/pgSQL based stored procedures?
Or any of you who wants to donate some?
---(end of broadcast)---
TIP 8: explain analyze is your friend
---(end of broadcast)---
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match
Re: [SQL] Returning a bool on DELETE in a proc.
> >So what variable/function is the correct SQL-equivalent to ROW_COUNT and > >can it be used in the following statement ? > >like DELETE...; SELECT (ROW_COUNT<>0); to return a bool value? > > SQL doesn't support that (although I suppose it could be made to with > some pg_rows_affected() function). If someone could only post that pg_rows_affected() function - this would end all further requests for "row number in result set" inquiries with the simple advice "use pg_rows_affected() and join that with an integer table". I'd know a bunch of uses right off the top of my head. I suppose it ain't hard at all but my C skills are way to limited to try anything like that. My understanding, too, likely. Karsten -- GPG key ID E4071346 @ wwwkeys.pgp.net E167 67FD A291 2BEA 73BD 4537 78B9 A9F9 E407 1346 ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] Looking for examples of S/P
On Jan 19, 2005, at 4:03 AM, KÖPFERL Robert wrote: In order to learn SQL-Stored Procedure techniqes I'm looking for a series of examples. Where can I find examples of SQL and PL/pgSQL based stored procedures? Or any of you who wants to donate some? The pgEdit distribution includes an extensive example for importing and analyzing web server logs using only SQL and PL/pgSQL. Look in the pgEdit/Examples/web folder after installation. http://pgedit.com/download John DeSoi, Ph.D. http://pgedit.com/ Power Tools for PostgreSQL ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
[SQL] Conversion ideas (Views/procedures)
I am new to Postgres and am converting our MSSQL database to Postgres. I just did my first function. My question is does it make sense to create functions for some of the common functionality available to the MSSQL world (left, isnull, etc) so I do not need visit every view, and stored procedure to remove them? If it does is there a body of work anywhere? If not what is the best approach just remove all non existent (re-engineer) functions from my views and procedures. I understand some of the items are available, but a bit different either the name of the function or its use. I did see a how to that alerted me to the datefiff being date_diff and to watch out for the yy needing to be 'year' etc. he mentioned to use coalesce() instead of isnull also. Any other common ones I should be aware of? Joel Fradkin Wazagua, LLC 2520 Trailmate Dr Sarasota, Florida 34243 Tel. 941-753-7111 ext 305 [EMAIL PROTECTED] www.wazagua.com Powered by Wazagua Providing you with the latest Web-based technology & advanced tools. © 2004. WAZAGUA, LLC. All rights reserved. WAZAGUA, LLC This email message is for the use of the intended recipient(s) and may contain confidential and privileged information. Any unauthorized review, use, disclosure or distribution is prohibited. If you are not the intended recipient, please contact the sender by reply email and delete and destroy all copies of the original message, including attachments. ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] Conversion ideas (Views/procedures)
As so often, I would suggest regular languages to accomplish most of the work that comes up. Use for example ours all friend 'sed' to edit a big sql-dump. you can for example simply replace isnull(X) by X is null. RegEx will possibly not reach for all posibilities but the're very good for the first big steps towards your goal. > -Original Message- > From: Joel Fradkin [mailto:[EMAIL PROTECTED] > Sent: Mittwoch, 19. Jänner 2005 16:23 > To: pgsql-sql@postgresql.org > Subject: [SQL] Conversion ideas (Views/procedures) > > > I am new to Postgres and am converting our MSSQL database to Postgres. > > I just did my first function. > My question is does it make sense to create functions for > some of the common > functionality available to the MSSQL world (left, isnull, > etc) so I do not > need visit every view, and stored procedure to remove them? > If it does is there a body of work anywhere? > If not what is the best approach just remove all non existent > (re-engineer) > functions from my views and procedures. I understand some of > the items are > available, but a bit different either the name of the > function or its use. I > did see a how to that alerted me to the datefiff being > date_diff and to > watch out for the yy needing to be 'year' etc. he mentioned to use > coalesce() instead of isnull also. Any other common ones I > should be aware > of? > > > Joel Fradkin > > Wazagua, LLC > 2520 Trailmate Dr > Sarasota, Florida 34243 > Tel. 941-753-7111 ext 305 > > [EMAIL PROTECTED] > www.wazagua.com > Powered by Wazagua > Providing you with the latest Web-based technology & advanced tools. > © 2004. WAZAGUA, LLC. All rights reserved. WAZAGUA, LLC > This email message is for the use of the intended > recipient(s) and may > contain confidential and privileged information. Any > unauthorized review, > use, disclosure or distribution is prohibited. If you are > not the intended > recipient, please contact the sender by reply email and > delete and destroy > all copies of the original message, including attachments. > > > > > > > ---(end of > broadcast)--- > TIP 4: Don't 'kill -9' the postmaster > ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [SQL] Returning a bool on DELETE in a proc.
On Wed, Jan 19, 2005 at 11:24:26AM +0100, KÖPFERL Robert wrote: > Seems that I forgot to mention that this function is intended to be written > in SQL, not in PL/SQL. Why SQL instead of PL/pgSQL if the former doesn't do what you need and the latter does? -- Michael Fuhr http://www.fuhr.org/~mfuhr/ ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [SQL] Returning a bool on DELETE in a proc.
It's ... hm I have the feeling that even complicated and boxed queries are faster or can be optimized by the planer than any PLpgsql. Isn't there a motule that has to be invoked? I'm capable to learn. > -Original Message- > From: Michael Fuhr [mailto:[EMAIL PROTECTED] > Sent: Mittwoch, 19. Jänner 2005 18:02 > To: KÖPFERL Robert > Cc: pgsql-sql@postgresql.org > Subject: Re: [SQL] Returning a bool on DELETE in a proc. > > > On Wed, Jan 19, 2005 at 11:24:26AM +0100, KÖPFERL Robert wrote: > > > Seems that I forgot to mention that this function is > intended to be written > > in SQL, not in PL/SQL. > > Why SQL instead of PL/pgSQL if the former doesn't do what you need > and the latter does? > > -- > Michael Fuhr > http://www.fuhr.org/~mfuhr/ > ---(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
Re: [SQL] Conversion ideas (Views/procedures)
> I am new to Postgres and am converting our MSSQL database to Postgres. > > I just did my first function. > My question is does it make sense to create functions for some of > the common functionality available to the MSSQL world (left, isnull, > etc) so I do not need visit every view, and stored procedure to > remove them? If it does is there a body of work anywhere? If not > what is the best approach just remove all non existent (re-engineer) > functions from my views and procedures. I understand some of the > items are available, but a bit different either the name of the > function or its use. I did see a how to that alerted me to the > datefiff being date_diff and to watch out for the yy needing to be > 'year' etc. he mentioned to use coalesce() instead of isnull also. > Any other common ones I should be aware of? > > Joel Fradkin > I like the idea myself of a 'library' that when placed first in the 'search path' would allow an application designed for a MSSQL database to operate. Obviously a similar library for Oracle, MySQL and others would also be benefical. They could have the affect of increasing PostgreSQL's use by reducing the immediate porting requirements. I do not know if this is possible but it would be cool stuff. Kind Regards, Keith __ 99main Internet Services http://www.99main.com ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
[SQL] ERROR: row is too big: size 9856, maximum size 8136
I get ERROR: row is too big: size 9856, maximum size 8136 when inserting a view? Help Joel Fradkin Wazagua, LLC 2520 Trailmate Dr Sarasota, Florida 34243 Tel. 941-753-7111 ext 305 [EMAIL PROTECTED] www.wazagua.com Powered by Wazagua Providing you with the latest Web-based technology & advanced tools. © 2004. WAZAGUA, LLC. All rights reserved. WAZAGUA, LLC This email message is for the use of the intended recipient(s) and may contain confidential and privileged information. Any unauthorized review, use, disclosure or distribution is prohibited. If you are not the intended recipient, please contact the sender by reply email and delete and destroy all copies of the original message, including attachments.
Re: [SQL] Returning a bool on DELETE in a proc.
On Wed, Jan 19, 2005 at 06:09:16PM +0100, KÖPFERL Robert wrote: > > > > Why SQL instead of PL/pgSQL if the former doesn't do what you need > > and the latter does? > > I have the feeling that even complicated and boxed queries are faster or can > be optimized by the planer than any PLpgsql. Isn't there a motule that has > to be invoked? PL/pgSQL might be more efficient than you think -- see the Overview in the PL/pgSQL documentation for more information about how it works. I'd suggest making the function work first, and only then worry about whether it's fast enough. -- Michael Fuhr http://www.fuhr.org/~mfuhr/ ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
[SQL] include in both ecpg and C++
I have a header file that gets included by both C/C++ code and ecpg code. In this header are a few structs that get used by both code (C/C++ and ecpg). In order to get this to work for Informix, we had to do something like this in the header file: #ifdef TFLG EXEC SQL BEGIN DECLARE SECTION #endif struct definitions #ifdef TFLG EXEC SQL END DECLARE SECTION #endif I use the EXEC SQL INCLUDE headerfile statement in my ecpg code, but of course, not the C/C++ code. I believe the TFLG macro gets defined by the Informix precompiler (though I have not been able to verify this). I also have not been able to find any sort of PostGres counterpart. I am using the Informix compatibility switch when compiling ecpg, but do not get a successful compile (with a number of compile variations attempted). Does anyone know how to implement such a design? Is there a PostGres macro that is basically the same thing or is there another method I may be able to use? Thanks, Tom ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [SQL] ERROR: row is too big: size 9856, maximum size 8136
On Wed, Jan 19, 2005 at 03:50:30PM -0500, Joel Fradkin wrote: > I get ERROR: row is too big: size 9856, maximum size 8136 when inserting a > view? Could you post the smallest possible self-contained example that demonstrates this behavior? What version of PostgreSQL are you using? -- Michael Fuhr http://www.fuhr.org/~mfuhr/ ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
[SQL] Query performance and understanding explain analzye
Hi,
I'm trying to tune a query that is taking to long to execute. I haven't done
much sql tuning and have only had a little exposure to explain and explain
analyze but from what I've read on the list and in books the following is
generally true:
Seq Scans are the almost always evil (except if a table has only a few
values) Nested Joins are generally evil as every node below it is executed
the number of times the "loops=" value says.
Hash Joins are extremely quick. This is because when postgres uses Hash
joins it creates a copy of the values of the table in memory and then Hashes
(some type of memory join) to the other table.
Is that correct?
If so, I'm after some help on the following query which I feel is taking too
long. At the outset I want to apologise for the length of this email, I just
wanted to provide as much info as possible. I just can't seem to make sense
of it and have been trying for days!
SELECT abs(item.area-userpolygon.area) as area,item.title as
item_title,item.id as item_id,item.collection_id as
item_collection_id,item.type_id as item_type_id,item.scale as
item_scale,publisher.publisher as publisher_publisher,publisher.description
as publisher_description,language.language as
language_language,language.description as
language_description,language.code2 as language_code2,language.code3 as
language_code3,collection.collection as
collection_collection,collection.description as
collection_description,item_base_type.type as
item_type_combination_type,item_subtype.subtype as
item_type_combination_subtype,item_format.format as
item_type_combination_format,status.status as
status_status,status.description as status_description,currency.code as
currency_code,currency.description as currency_description,item.subtitle as
item_subtitle,item.description as item_description,item.item_number as
item_item_number,item.edition as item_edition,item.h_datum as
item_h_datum,item.v_datum as item_v_datum,item.projection as
item_projection,item.isbn as item_isbn,client_item_field.stock as
client_item_field_stock,client_item_field.price as
client_item_field_price,client_freight.freight as
client_freight_freight,client_freight.description as
client_freight_description FROM item INNER JOIN (client INNER JOIN
client_item ON (client.id=client_item.client_id)) ON
(client_item.item_id=item.id ) INNER JOIN publisher ON (item.publisher_id =
publisher.id) INNER JOIN language ON (item.language_id = language.id) LEFT
OUTER JOIN collection ON (item.collection_id = collection.id) INNER JOIN
item_base_type ON (item.type_id = item_base_type.id) INNER JOIN item_subtype
ON (item.subtype_id = item_subtype.id) INNER JOIN item_format ON
(item.format_id = item_format.id) INNER JOIN status ON (item.status_id =
status.id) INNER JOIN currency ON (item.publisher_currency_id = currency.id)
LEFT OUTER JOIN client_item_field ON
(client_item.client_id=client_item_field.client_id) AND
(client_item.item_id=client_item_field.item_id)
LEFT OUTER JOIN client_item_freight ON
(client_item.client_id=client_item_freight.client_id) AND
(client_item.item_id=client_item_freight.item_id)
LEFT OUTER JOIN client_freight ON
(client_freight.id=client_item_freight.client_freight_id), userpolygon WHERE
item.the_geom && userpolygon.the_geom AND distance(item.the_geom,
userpolygon.the_geom)=0 AND userpolygon.session_id='TestQuery'
AND client.id=1 ORDER BY area asc
When I explain analyze it I get:
QUERY PLAN
Sort (cost=4793.89..4793.91 rows=7 width=622) (actual
time=4066.52..4067.79 rows=4004 loops=1)
Sort Key: abs((item.area - userpolygon.area))
-> Nested Loop (cost=533.45..4793.79 rows=7 width=622) (actual
time=66.89..4054.01 rows=4004 loops=1)
Join Filter: (("outer".the_geom && "inner".the_geom) AND
(distance("outer".the_geom, "inner".the_geom) = 0::double precision))
-> Hash Join (cost=533.45..4548.30 rows=14028 width=582) (actual
time=63.79..3826.16 rows=14028 loops=1)
Hash Cond: ("outer".client_freight_id = "inner".id)
-> Hash Join (cost=532.38..4437.64 rows=14028 width=540)
(actual time=63.52..3413.48 rows=14028 loops=1)
Hash Cond: ("outer".item_id = "inner".item_id)
Join Filter: ("outer".client_id = "inner".client_id)
-> Hash Join (cost=532.38..4367.49 rows=14028
width=528) (actual time=62.95..2993.37 rows=14028 loops=1)
Hash Cond: ("outer".item_id = "inner".item_id)
Join Filter: ("outer".client_id =
"inner".client_id)
-> Hash Join (cost=532.38..4297.33 rows=14028
width=508) (actual time=62.48..2576.46 rows=14028 loops=1)
Hash Cond: ("outer".publisher_currency_i
