Re: Learning Plpgsql ??

I would suggest using the pgAdminII Migration Wizard.  It will create all the sequences for you and sets them to the appropriate values (max primary key).  I just completed a migration from MS SQL Server and it was fantastic.  Very, very easy - as long as you don't have binary data, which was an incredible pain.
http://pgadmin.postgresql.org/
 
As far as learning PL/pgSQL goes....I'm having trouble with that too.  The examples in the docs aren't very helpful, I've had to search the mailing list archives a lot.  There should be a well defined example of how to return a ResultSet from a function, for one thing.  I found this message in the archives very helpful (and think it should be added to the docs):
http://archives.postgresql.org/pgsql-hackers/2002-09/msg01741.php
 
Whereas the docs describe how to do it, but don't show the actual code (in 19.6.1):
http://www.postgresql.org/docs/view.php?version=7.3&file=plpgsql-control-structures.html
 
Roman Fail
Sr. Web Application Developer
POS Portal, Inc.
Sacramento, CA USA

	On Thu, 2003-01-23 at 14:18, Jonathon Batson wrote:
	> Hi
	> Newbie to Plpgsql and Postgres.
	> I am porting dbs from Access97 to postgres, tables and data comes across
	> fine but the autonumber(sequences) do not
	> get updated, so I need to do this manually using > SELECT
	> setval(sequence_name,value);
	> OK , this is no problem, BUT, I have 90 tables in the db, and around 70
	> clients to port to postgres.
	> So a function to do this is needed.
	>
	> The direction took so far is to create a  table, seq_table consisting of
	>  all the sequences information in the db as follows:
	>
	>     sequence_name                    table_name            pk_column
	> -----------------------------------------------------------------
	> customer_number_seq                customer               c_number
	> purchase_job_number_seq         purchase                job_number
	> etc
	>
	> Then a function that in psuedo code is something like this
	> for each row in seq_table
	>     get max(pk_column) from table_name
	>     set sequence_name to max
	> endfor
	>   
	> So my function is:
	>
	> -- Function: update_seq()
	> CREATE or REPLACE FUNCTION update_seq() RETURNS text AS '
	>   DECLARE
	>      row RECORD;   
	>      maxid INTEGER;
	> 
	>   BEGIN
	>      -- select sequence information [ sequence_name, table_name, pk_column]
	>     FOR row IN SELECT * FROM seq_table LOOP
	>
	>        --  get the maxid for row.table_name on row.pkcolumn
	>        SELECT max(row.pk_column) INTO maxid FROM row.table_name;
	>   
	>        -- then set the sequence value
	>        SELECT setval(row.sequence_name,maxid);
	>
	>     END LOOP;
	>
	>     RETURN ''done'';
	>   END;
	> '  LANGUAGE 'plpgsql';
	
	
	I think you will need to build the queries as text and use EXECUTE to
	EXECUTE them:
	
	DECLARE
	  dqry TEXT;
	
	...
	
	  dqry := ''SELECT setval('''' || row.sequence_name || '''', '' || maxid
	|| '');'';
	  EXECUTE dqry;
	
	
	Section 19.5.4 of the docs.
	
	Cheers,
	                                        Andrew.
	
	>
	> The function fails at the line select into line
	> SELECT max(row.pk_column) INTO maxid FROM row.table_name;
	> with the following error........something to do with the second var
	> row.table_name I think.
	>
	> swimdb=# SELECT update_seq();
	> NOTICE:  Error occurred while executing PL/pgSQL function update_seq
	> NOTICE:  line 14 at select into variables
	> ERROR:  parser: parse error at or near "$2"
	>
	> Any ideas would be gratefully accepted...
	> Also a direction on some more detailed PL/pgSQL documentation
	> Have looked a Postgres Interactive Docs..not so helpfull
	>
	> Thanks
	> Jonathon