Hi all,
I've come accross a problem with pg_get_serial_sequence(table,
serial_column) when dealing with inherited tables if the serial was
defined in the parent table. The function would not return the serial
name in this case if the child table is specified.
To solve it, and improve its usability for all you MSSQL converts using
the typical and common-sense single serial identifier and primary key (I
wonder what would you want to use two serials in one table?), a plpgsql
function to retrieve the sequence for whatever it is the first serial
available for a given table (it actually works with any column with a
default defined for nextval('whatever'::text) ). Following is the code
for it, I hope you find it useful. Get the last inserted value as
"SELECT currval(f_get_serial_seq_name(reloid('public', 'mytable', 'r')))
AS last_id".
CREATE OR REPLACE FUNCTION "reloid" (p_schemaname name, p_relname name,
p_relkind name) RETURNS oid AS
$body$
SELECT oid FROM pg_class
WHERE relkind = $3 AND relname = $2 AND relnamespace = gbt.schemaoid($1);
$body$
LANGUAGE 'sql' VOLATILE RETURNS NULL ON NULL INPUT SECURITY INVOKER;
create or replace function f_get_serial_seq_name(p_reloid oid) returns
name as $$
DECLARE
_atdef pg_attrdef;
BEGIN
FOR _atdef IN SELECT * FROM pg_attrdef WHERE adrelid = p_reloid AND
adsrc like 'nextval(%_seq''::text)' ORDER BY adnum LIMIT 1 LOOP
RETURN substring(_atdef.adsrc from 'nextval#(''#"%#"''::text#)' for
'#');
END LOOP;
RETURN null;
END; $$ language plpgsql;
comment on function gbt.f_get_serial_seq_name(oid) is
'Similar to pg_get_serial_sequence, but assumes always the sequence for
the first column
that has a nextval for any sequence named [...]_seq, and regardless of
having the column been defined as serial or not.'
Please post comments, I think it can be further optimised and enhanced
quite a bit.
Cheers!
Ezequiel Tolnay
