From: | Raymond O'Donnell <rod(at)iol(dot)ie> |
---|---|
To: | "J(dot)V(dot)" <jvsrvcs(at)gmail(dot)com> |
Cc: | pgsql-general <pgsql-general(at)postgresql(dot)org>, Merlin Moncure <mmoncure(at)gmail(dot)com> |
Subject: | Re: how to create a sequence in a stored proc? |
Date: | 2012-04-27 17:05:57 |
Message-ID: | 4F9AD1F5.6000401@iol.ie |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
On 27/04/2012 17:35, J.V. wrote:
> Right, I understand that fully, and have used SQL inside a stored proc
> before, but in this case as I mentioned, I need to first do a select
> from a table to get a max value, store that in a variable and then use
> that variable in a create sequence sql statement.
Something like this, off the top of my head and untested:
create or replace function make_sequence_for_table()
returns void
as
$$
declare
max_value integer;
begin
select max(my_column) into max_value from my_table;
create sequence my_sequence
start (max_value + 1)
owned by my_table.my_column;
alter table my_table
alter column my_column
set default nextval('my_sequence');
return;
end;
$$
language plpgsql;
For extra marks, pass the table name in as a parameter, construct the
SQL as a string and execute it using EXECUTE (the pl/pgsql version, not
the command for executing prepared statements).
Ray.
--
Raymond O'Donnell :: Galway :: Ireland
rod(at)iol(dot)ie
From | Date | Subject | |
---|---|---|---|
Next Message | Steve Atkins | 2012-04-27 17:13:16 | Re: how to create a sequence in a stored proc? |
Previous Message | Merlin Moncure | 2012-04-27 16:57:08 | Re: how to create a sequence in a stored proc? |