Re: [SQL] [GENERAL] Looking for information on PostgreSQL Stored Procedures
Michael, This is one of the simple ones that has me hung up. It currently errors out on the first line. Why I have no idea. It looks right to me. One of the harder ones that I have to work on is on the security sub-routines to pull together the security rights for a person during login and I'm looking forward to that one. I'll take a look at the reference that you sent. The other ones that I wrote had to be really simple if I'm hung on this one. It is just a simple purge on a temp table before merging new information into the master table. I have thought about this before hand wasn't about to locate the "Welcome to PostreSQL Stored Procedures" or in my current case "PostreSQL for idiots". This is embarrassing for the time I've spend as a DBA. Getting kicked by a simple pre-process procedure. CREATE FUNCTION sp_removedups() RETURNS void AS $BODY$ DECLARE lastname varchar(255); fname varchar(255); id bigint; DECLARE NewListCursor CURSOR FOR SELECT Name, id FROMMailingList ORDER BY Name; OPEN NewListCursor; LineNum := 0; LastName := ""; FETCH NEXT FROM NewListCursor INTO fname, id; WHILE (--Lost on variable name for end of query; EmptyQueryResponse <> 0? --) BEGIN IF LastName = fname THEN DELETE FROM MailingList WHERE id = id; END IF; LastName := fname; FETCH NEXT FROM NewListCursor INTO fname, id; END; CLOSE NewListCursor; $BODY$ LANGUAGE 'sql' VOLATILE; -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Michael Fuhr Sent: Saturday, December 10, 2005 10:41 PM To: Foster, Stephen Cc: [email protected]; [email protected] Subject: Re: [SQL] [GENERAL] Looking for information on PostgreSQL Stored Procedures On Sat, Dec 10, 2005 at 09:02:39PM -0600, Foster, Stephen wrote: > I did see last week something on PLPGSQL and read through that. But > there has to be something out there that goes in depth on the > SQL/Function command set(Speaking of functions/procedures). The standard functions are described in the "Functions and Operators" chapter of the documentation. Here's a link to the latest version, but use the documentation for the version you're running: http://www.postgresql.org/docs/8.1/interactive/functions.html SQL functions are documented in "Query Language (SQL) Functions": http://www.postgresql.org/docs/8.1/interactive/xfunc-sql.html > The biggest hole that I have; seems to be on Cursors; define and > opening. I think the fetching and closing is pretty straight forward. > But the Define and opening is causing some grief. Either I'm making to > far too hard or I'm really have missing something silly. What problems are you having? Without seeing what you're doing in PostgreSQL it's difficult to say what's wrong. In PL/pgSQL you can loop through query results without explicitly using a cursor; see "Looping Through Query Results": http://www.postgresql.org/docs/8.1/interactive/plpgsql-control-structure s.html#PLPGSQL-RECORDS-ITERATING [snip example] > This is an example of the simple stored procedures like the ones I'm > trying to migrate. PLPGSQL is ok but I thought it would run better in > SQL. SQL doesn't have control structures, so if you need conditionals or loops then use a procedural language like PL/pgSQL or implement the logic on the client side. For the example you posted, the following query should have the same effect (remove records with duplicate names, if I'm reading it right): DELETE FROM mailinglist WHERE id NOT IN ( SELECT DISTINCT ON (name) id FROM mailinglist ORDER BY name, id ); I don't know how well this would perform on large data sets, especially in older versions of PostgreSQL, but you could try it. I'd recommend trying it first on a test table or in a transaction that you can roll back in case it doesn't do what you want. See the SELECT documentation for a description of the non-standard DISTINCT ON clause that the above query uses: http://www.postgresql.org/docs/8.1/interactive/sql-select.html -- Michael Fuhr ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq -- No virus found in this incoming message. Checked by AVG Free Edition. Version: 7.1.371 / Virus Database: 267.13.13/197 - Release Date: 12/9/2005 -- No virus found in this outgoing message. Checked by AVG Free Edition. Version: 7.1.371 / Virus Database: 267.13.13/197 - Release Date: 12/9/2005 ---(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: [SQL] [GENERAL] Looking for information on PostgreSQL Stored Procedures
Ok, I tried it in that I'm still getting errors. Happy to do so but well is my errors? -Original Message- From: Douglas McNaught [mailto:[EMAIL PROTECTED] Sent: Sunday, December 11, 2005 10:39 AM To: Foster, Stephen Cc: 'Michael Fuhr'; [email protected]; [email protected] Subject: Re: [SQL] [GENERAL] Looking for information on PostgreSQL Stored Procedures "Foster, Stephen" <[EMAIL PROTECTED]> writes: > WHILE (--Lost on variable name for end of query; EmptyQueryResponse <> > 0? --) > BEGIN > IF LastName = fname THEN > DELETE FROM MailingList WHERE id = id; > END IF; > LastName := fname; > FETCH NEXT FROM NewListCursor INTO fname, id; > END; > CLOSE NewListCursor; > $BODY$ > LANGUAGE 'sql' VOLATILE; You can't do any looping or other control structures in an SQL function. Use PL/pgSQL instead. -Doug -- No virus found in this incoming message. Checked by AVG Free Edition. Version: 7.1.371 / Virus Database: 267.13.13/197 - Release Date: 12/9/2005 -- No virus found in this outgoing message. Checked by AVG Free Edition. Version: 7.1.371 / Virus Database: 267.13.13/197 - Release Date: 12/9/2005 ---(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: [SQL] [GENERAL] Looking for information on PostgreSQL Stored Procedures
"Foster, Stephen" <[EMAIL PROTECTED]> writes: > This is one of the simple ones that has me hung up. It currently errors > out on the first line. Why I have no idea. It looks right to me. You should show us the error message; most of us are not psychics. > CREATE FUNCTION sp_removedups() RETURNS void AS > $BODY$ > DECLARE lastname varchar(255); > fname varchar(255); > id bigint; > DECLARE NewListCursor CURSOR FOR > SELECT Name, id > FROMMailingList > ORDER BY Name; > OPEN NewListCursor; You're missing a BEGIN, and I'm not sure whether it's allowed to use the DECLARE keyword twice in the same block (the second instance is certainly unnecessary even if legal). regards, tom lane ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [SQL] [GENERAL] Looking for information on PostgreSQL Stored Procedures
On Sun, Dec 11, 2005 at 11:38:47AM -0500, Douglas McNaught wrote: > "Foster, Stephen" <[EMAIL PROTECTED]> writes: > > > WHILE (--Lost on variable name for end of query; EmptyQueryResponse <> > > 0? --) > > BEGIN > > IF LastName = fname THEN > > DELETE FROM MailingList WHERE id = id; > > END IF; > > LastName := fname; > > FETCH NEXT FROM NewListCursor INTO fname, id; > > END; > > CLOSE NewListCursor; > > $BODY$ > > LANGUAGE 'sql' VOLATILE; > > You can't do any looping or other control structures in an SQL > function. Use PL/pgSQL instead. And as I mentioned in my previous post, you can loop through query results without messing around with an explicit cursor. CREATE FUNCTION testfunc() RETURNS void AS $$ DECLARE row record; BEGIN FOR row IN SELECT * FROM tablename ORDER BY whatever LOOP -- do stuff that refers to row.column_name END LOOP; RETURN; END; $$ LANGUAGE plpgsql; -- Michael Fuhr ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [SQL] [GENERAL] Looking for information on PostgreSQL Stored Procedures
Trying a totally different approach. Simple procedure that I'm using to use as learn opportunity in stored procedures in PostgreSQL prior to migrating a complex web site. Call it a training example if you will. Goal is to learn the correct process of working with cursors on complex queries in PostgreSQL before investing hours of work migrating a web site and moving some of the complex procedures to the database to simplify a process. Using this simple example, CREATE FUNCTION sp_removedups() RETURNS void AS $BODY$ DECLARE lastname varchar(255); fname varchar(255); id bigint; DECLARE NewListCursor CURSOR FOR SELECT Name, id FROMMailingList ORDER BY Name; BEGIN OPEN NewListCursor; LastName := ""; FETCH NEXT FROM NewListCursor INTO fname, id; WHILE (--Lost on variable name for end of query; EmptyQueryResponse <> 0? --) BEGIN IF LastName = fname THEN DELETE FROM MailingList WHERE id = id; END IF; LastName := fname; FETCH NEXT FROM NewListCursor INTO fname, id; END; CLOSE NewListCursor; END; $BODY$ LANGUAGE 'plpgsql' VOLATILE; On the BEGIN/END; yes you are right it was missing. I'm converting a MS-2000 SQL example to PostgreSQL. Error according to pgadminIII is on line 11. Which should be the "LastName := "";" line. Before someone says it there is a better way of do this but it was a simple example before I dived in to the real ones. Second question/problem is how do you determine if the query has reached the end. Mentioned on the WHILE line. I have another project after this one I'm about to tackle that will be even more complex. So the sooner I can grasp the store procedures in this database the better off I will be. If there is a book somewhere that will clearly define the command set please let me know. This way determine what I can and can't do. The web language I use I can go back and fore with no problems but am think some of the processing that it is currently doing would be faster if I move it to the database side. Thanks for any help, Lee Foster -- No virus found in this outgoing message. Checked by AVG Free Edition. Version: 7.1.371 / Virus Database: 267.13.13/197 - Release Date: 12/9/2005 ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [SQL] [GENERAL] Looking for information on PostgreSQL Stored Procedures
Thanks Michael! Guess I should have drunk more coffee before replying. I must have miss that. -Original Message- From: Michael Fuhr [mailto:[EMAIL PROTECTED] Sent: Sunday, December 11, 2005 11:22 AM To: Douglas McNaught Cc: Foster, Stephen; [email protected]; [email protected] Subject: Re: [SQL] [GENERAL] Looking for information on PostgreSQL Stored Procedures On Sun, Dec 11, 2005 at 11:38:47AM -0500, Douglas McNaught wrote: > "Foster, Stephen" <[EMAIL PROTECTED]> writes: > > > WHILE (--Lost on variable name for end of query; EmptyQueryResponse <> > > 0? --) > > BEGIN > > IF LastName = fname THEN > > DELETE FROM MailingList WHERE id = id; > > END IF; > > LastName := fname; > > FETCH NEXT FROM NewListCursor INTO fname, id; > > END; > > CLOSE NewListCursor; > > $BODY$ > > LANGUAGE 'sql' VOLATILE; > > You can't do any looping or other control structures in an SQL > function. Use PL/pgSQL instead. And as I mentioned in my previous post, you can loop through query results without messing around with an explicit cursor. CREATE FUNCTION testfunc() RETURNS void AS $$ DECLARE row record; BEGIN FOR row IN SELECT * FROM tablename ORDER BY whatever LOOP -- do stuff that refers to row.column_name END LOOP; RETURN; END; $$ LANGUAGE plpgsql; -- Michael Fuhr -- No virus found in this incoming message. Checked by AVG Free Edition. Version: 7.1.371 / Virus Database: 267.13.13/197 - Release Date: 12/9/2005 -- No virus found in this outgoing message. Checked by AVG Free Edition. Version: 7.1.371 / Virus Database: 267.13.13/197 - Release Date: 12/9/2005 ---(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: [SQL] [GENERAL] Looking for information on PostgreSQL Stored Procedures
"Foster, Stephen" <[EMAIL PROTECTED]> writes: > WHILE (--Lost on variable name for end of query; > EmptyQueryResponse <> 0? --) WHILE FOUND LOOP ... END LOOP Although as Michael mentioned, this is all the hard way; any experienced plpgsql programmer would forget the cursor entirely and just write a FOR-loop. regards, tom lane ---(end of broadcast)--- TIP 6: explain analyze is your friend
