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