Message-ID: <20030630145012.88168.qmail@web41707.mail.yahoo.com>
Date: Mon, 30 Jun 2003 07:50:12 -0700 (PDT)
From: Erik Erkelens <erik_erkelens@yahoo.com>
Subject: CREATE SEQUENCE fails in plpgsql function
To: pgsql-sql@postgresql.org
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii

Hi,

I need a table to hold the last n INSERTs into it. 

To keep track of how many entries there are, and to
provide a unique id to order the records in the table,
I use a sequence. A trigger function deletes and entry
on an insert if the table is full.  

The number n maybe changed, so I implemented this
PL/PgSQL function:

CREATE OR REPLACE FUNCTION set_max_records(integer)
RETURNS integer AS '
    DECLARE 
        new_max_records ALIAS FOR $1;
    BEGIN
        DROP SEQUENCE my_sequence;
        --CREATE SEQUENCE my_sequence MAXVALUE 4
CYCLE;
        CREATE SEQUENCE my_sequence MAXVALUE
new_max_records CYCLE;
        RETURN 0;
    END;
    
' LANGUAGE 'plpgsql';

(I left out the part where the table is shrunk and
renumbered if n goes down).


rdb=# select set_max_records(3);
LOG:  query: CREATE SEQUENCE my_sequence MAXVALUE  $1 
CYCLE
LOG:  statement: select set_max_records(3);
WARNING:  Error occurred while executing PL/pgSQL
function set_max_records
LOG:  statement: select set_max_records(3);
WARNING:  line 6 at SQL statement
LOG:  statement: select set_max_records(3);
DEBUG:  AbortCurrentTransaction
ERROR:  parser: parse error at or near "$1" at
character 39

If I don't use the variable new_max_records, it works
(the commented out line). What could be the problem ?

Also, if there is a better mechanism to implement
this, I'm all ears...

Erik Erkelens.

__________________________________
Do you Yahoo!?
SBC Yahoo! DSL - Now only $29.95 per month!
http://sbc.yahoo.com