Re: [COMMITTERS] pgsql-server: Clean up generation of default

> 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;