Обсуждение: Learning Plpgsql ??

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';

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

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
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 2: you can get off all lists at once with the unregister command
>     (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)
--
---------------------------------------------------------------------
Andrew @ Catalyst .Net.NZ Ltd, PO Box 11-053, Manners St,  Wellington
WEB: http://catalyst.net.nz/         PHYS: Level 2, 150-154 Willis St
DDI: +64(4)916-7201     MOB: +64(21)635-694    OFFICE: +64(4)499-2267
           Survey for nothing with http://survey.net.nz/
---------------------------------------------------------------------

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
    

Coming from another newbie, here's what I use to set the sequence
following an import of text data.  I create seqence and table and import
one file at a time, for a one time conversion, so I don't need the table
of table names,etc., but that's a great idea, like a data dictionary
concept.  Would this work, modified to fit your loop syntax?

select SETVAL('link_rep_link_rep_id_seq', (select max(link_rep_id) from
link_rep))

Jim

Hi Jonathan

I've been using Postgres for a bit but I'm not really an expert.

You have probably seen that the "SERIAL" type expands to
    'not null default nextval('"auto_generated_index_name"'::text)

If you have imported a large set of records with explicit ids you could
simply use the 'setval(index_name,number)' function to reset the sequence's
current value.

Cheers
Rory

On 23/01/03, Jonathon Batson (jonathon@octahedron.com.au) wrote:
> 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);
--
Rory Campbell-Lange
<rory@campbell-lange.net>
<www.campbell-lange.net>

Jim Beckstrom <jrbeckstrom@sbcglobal.net> writes:

> Coming from another newbie, here's what I use to set the sequence
> following an import of text data.  I create seqence and table and
> import one file at a time, for a one time conversion, so I don't need
> the table of table names,etc., but that's a great idea, like a data
> dictionary concept.  Would this work, modified to fit your loop syntax?
>
> select SETVAL('link_rep_link_rep_id_seq', (select max(link_rep_id)
> from link_rep))
>
> Jim

For really large tables

SELECT SETVAL('link_rep_link_rep_id_seq', (SELECT link_rep_id
FROM link_rep ORDER BY link_rep_id DESC LIMIT 1))

will be quite a bit faster (you'll save yourself a tablescan).

Good Idea and it worked, had to play with quotes a bit, duh,
Note: the use of  quote_literal for seq's and quote_ident for table and
column objects.

CREATE or REPLACE FUNCTION up_seq() RETURNS text AS '
   DECLARE
      row RECORD;     qrystr TEXT;
   BEGIN
      -- select sequence information from seq table
    [ sequence_name, table_name, pk_column]
     FOR row IN SELECT * FROM swim_seq_temp LOOP

         qrystr :=
             ''SELECT setval(''
             || quote_literal(row.sequence_name)
             || '', ( SELECT max(''
             || quote_ident(row.pk_column)
             || '') FROM ''
             || quote_ident(row.table_name)
             || ''))'';
         EXECUTE qrystr;

     END LOOP;
   RETURN ''done'';
   END;
'  LANGUAGE 'plpgsql';





Jim Beckstrom wrote:

 > Coming from another newbie, here's what I use to set the sequence
 > following an import of text data.  I create seqence and table and
 > import one file at a time, for a one time conversion, so I don't need
 > the table of table names,etc., but that's a great idea, like a data
 > dictionary concept.  Would this work, modified to fit your loop syntax?
 >
 > select SETVAL('link_rep_link_rep_id_seq', (select max(link_rep_id)
 > from link_rep))
 >
 > Jim
 >
 >
 >
 > ---------------------------(end of broadcast)---------------------------
 > TIP 6: Have you searched our list archives?
 >
 > http://archives.postgresql.org
 >

Just an update on the set sequences for multiple tables across many dbs
function.
Would fail on empty tables, corrected...
Now has an output table that shows sequence value before, after, and
record count.
Feels more complete now..

-- Function: update_seq()
CREATE or REPLACE FUNCTION update_seq() RETURNS text AS '
  DECLARE
     qrystr0 TEXT;      qrystr1 TEXT;             qrystr2
TEXT;             qrystr3 TEXT;      qrystr4 TEXT;
     row RECORD;     seq_val RECORD;     next_seq RECORD;   no_recs RECORD;
     next_seq_val INTEGER;   no_records INTEGER;

  BEGIN
    -- output table is seq_values ( seq_name text, old_val int4, new_val
int4, record_no int4)
    qrystr0 := ''TRUNCATE seq_values'';
    EXECUTE qrystr0;

     -- select sequence information from seq table
    --  [ sequence_name, table_name, pk_column]
    FOR row IN SELECT * FROM swim_sequences LOOP

        -- retrieve next_seq value, need to use FOR IN EXECUTE LOOP to
retrieve data from dynamic queries
        qrystr1 := ''SELECT nextval('' ||
quote_literal(row.sequence_name) || '')'';
        FOR next_seq IN EXECUTE qrystr1 LOOP
            next_seq_val := next_seq.nextval;
        END LOOP;

        -- retrieve record count
        qrystr2 := ''SELECT count('' || quote_ident(row.pk_column) || ''
) FROM '' || quote_ident(row.table_name);
        FOR no_recs IN EXECUTE qrystr2 LOOP
            no_records := no_recs.count;
        END LOOP;

       -- set new seq value
       qrystr3 :=
            ''SELECT setval(''
            || quote_literal(row.sequence_name)
            || '', ( SELECT max(''
            || quote_ident(row.pk_column)
            || '') FROM ''
            || quote_ident(row.table_name)
            || ''))'';

        FOR seq_val IN EXECUTE qrystr3 LOOP
            -- covering errors for empty tables
            IF seq_val.setval IS NULL THEN
                seq_val.setval := 0;
                next_seq_val := 0;
            ELSE
                -- to give the current seq_val --not next_val
                next_seq_val := next_seq_val -1;
            END IF;
            -- update output table
            qrystr4 :=
                    ''INSERT INTO seq_values ( seq_name, old_val,
new_val,record_no ) VALUES (''
                    || quote_literal(row.sequence_name) || '',''
                    || quote_literal(next_seq_val) || '',''
                    || quote_literal(seq_val.setval) || '',''
                    || quote_literal(no_records) || '')'';
            EXECUTE qrystr4;

        END LOOP;

    END LOOP;

  RETURN ''done'';
  END;
'  LANGUAGE 'plpgsql';

Output table is...........

            seq_name                   | old_val | new_val | record_no
---------------------------------+---------+---------+----------
 customer_number_seq           |    0      |     5913   |      5913
 purchase_job_number_seq    |    0      |    8905    |      7644
 receipt_id_seq                       |    0      |    8722    |      8396
 account_options_id_seq         |    0      |       1       |         1

I took this code from a different message:

>CREATE or REPLACE FUNCTION up_seq() RETURNS text AS '
>   DECLARE
>      row RECORD;     qrystr TEXT;
>   BEGIN
>      -- select sequence information from seq table
>     [ sequence_name, table_name, pk_column]
>     FOR row IN SELECT * FROM swim_seq_temp LOOP
>
>         qrystr :=
>             ''SELECT setval(''
>             || quote_literal(row.sequence_name)
>             || '', ( SELECT max(''
>             || quote_ident(row.pk_column)
>             || '') FROM ''
>             || quote_ident(row.table_name)
>             || ''))'';
>         EXECUTE qrystr;
>
>     END LOOP;
>   RETURN ''done'';
>   END;
>'  LANGUAGE 'plpgsql';

I'm heartened that we have the capabilities to execute dynamic a string
like this. However, in SQL Server, this sort of operation slows down the
database, because it has to produce a new execution plan. Is the same true
for Postgresql?

The way SQL Server gets some time back is by the use of sp_executesql. Is
there some equivalent in Postgresql?

Steve

19.5.4. Executing dynamic queries