From: Erik Erkelens <erik_erkelens@yahoo.com>
To: pgsql-sql@postgresql.org
Subject: CREATE SEQUENCE fails in plpgsql function
Date: Mon, 30 Jun 2003 07:50:12 -0700 (PDT)
Message-ID: <20030630145012.88168.qmail@web41707.mail.yahoo.com> (raw)
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: erik_erkelens@yahoo.com
Subject: Re: CREATE SEQUENCE fails in plpgsql function
In-Reply-To: <20030630145012.88168.qmail@web41707.mail.yahoo.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox