Re: Learning Plpgsql ??

I would suggest using the pgAdminII Migration Wizard.  It will create all the sequences for you and sets them to the
appropriatevalues (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'vehad to search the mailing list archives a lot.  There should be a well defined example of how to return a ResultSet
froma 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