> I'd be inclined to make it only take 2 args, table, col where table can be
> namespace qualified. This allows people who arn't namespace aware to just do
> SELECT pg_get_serial_sequence('mytable','mycol') and have it return the
> correct item following searchpath.. I would think this would then become
> consistant with the standard behavior. Not to mention it would also allow
> for easier moving schema form one namespace to another..
OK, attached is a file with the original function, and an overloaded one
that just takes table and column. It searches your current search_path
to find the first matching table.
Tom, do you have any opinion on whether the former or latter function
would be a good solution to the pg_dump issue?
Chris
CREATE FUNCTION pg_get_serial_sequence(name, name, name) RETURNS text
AS '
SELECT
pg_catalog.quote_ident(pn_seq.nspname) || ''.'' || pg_catalog.quote_ident(seq.relname)
FROM
pg_catalog.pg_namespace pn,
pg_catalog.pg_class pc,
pg_catalog.pg_attribute pa,
pg_catalog.pg_depend pd,
pg_catalog.pg_class seq,
pg_catalog.pg_namespace pn_seq
WHERE
pn.nspname=$1
AND pc.relname=$2
AND pa.attname=$3
AND pn.oid=pc.relnamespace
AND pc.oid=pa.attrelid
AND pd.objid=seq.oid
AND pd.classid=seq.tableoid
AND pd.refclassid=seq.tableoid
AND pd.refobjid=pc.oid
AND pd.refobjsubid=pa.attnum
AND pd.deptype=''i''
AND seq.relkind=''S''
AND seq.relnamespace=pn_seq.oid
'
LANGUAGE sql;
CREATE FUNCTION pg_get_serial_sequence(name, name) RETURNS text
AS '
SELECT
pg_catalog.quote_ident(pn_seq.nspname) || ''.'' || pg_catalog.quote_ident(seq.relname)
FROM
pg_catalog.pg_class pc,
pg_catalog.pg_attribute pa,
pg_catalog.pg_depend pd,
pg_catalog.pg_class seq,
pg_catalog.pg_namespace pn_seq
WHERE
pg_catalog.pg_table_is_visible(pc.oid)
AND pc.relname=$1
AND pa.attname=$2
AND pc.oid=pa.attrelid
AND pd.objid=seq.oid
AND pd.classid=seq.tableoid
AND pd.refclassid=seq.tableoid
AND pd.refobjid=pc.oid
AND pd.refobjsubid=pa.attnum
AND pd.deptype=''i''
AND seq.relkind=''S''
AND seq.relnamespace=pn_seq.oid
'
LANGUAGE sql;
