Re: Inverse of pg_get_serial_sequence?

From: Robert Haas <robertmhaas(at)gmail(dot)com>
To: Andres Freund <andres(at)2ndquadrant(dot)com>
Cc: "pgsql-hackers(at)postgresql(dot)org" <pgsql-hackers(at)postgresql(dot)org>
Subject: Re: Inverse of pg_get_serial_sequence?
Date: 2014-09-03 14:59:53
Message-ID: CA+TgmoYT0J02U1Dpo5evDUOAygmW+06=8a+-Lzvq-e2HHwkxTQ@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-hackers

On Wed, Sep 3, 2014 at 10:44 AM, Andres Freund <andres(at)2ndquadrant(dot)com> wrote:
> On 2014-09-03 09:31:50 -0400, Robert Haas wrote:
>> On Fri, Aug 29, 2014 at 1:26 PM, Andres Freund <andres(at)2ndquadrant(dot)com> wrote:
>> > We have pg_get_serial_sequence() mapping (relation, colum) to the
>> > sequence. What I'm missing right now is the inverse. I.e. given a
>> > sequence tell me the owner.
>> > describe.c has a query for that, and it's not too hard to write, but it
>> > still seems 'unfriendly' not to provide it.
>> >
>> > Does anybody dislike adding a function for that?
>>
>> I'll go out on a limb and say that it sounds like pointless catalog
>> bloat to me. I am all in favor of adding things like this where the
>> SQL query is painful to write (e.g. things involving pg_depend) but if
>> it's a simple SELECT query then, eh, not really excited about it.
>
> There's not really a simple select for it, is there? psql uses:
>
> /* Get the column that owns this sequence */
> printfPQExpBuffer(&buf, "SELECT pg_catalog.quote_ident(nspname) || '.' ||"
> "\n pg_catalog.quote_ident(relname) || '.' ||"
> "\n pg_catalog.quote_ident(attname)"
> "\nFROM pg_catalog.pg_class c"
> "\nINNER JOIN pg_catalog.pg_depend d ON c.oid=d.refobjid"
> "\nINNER JOIN pg_catalog.pg_namespace n ON n.oid=c.relnamespace"
> "\nINNER JOIN pg_catalog.pg_attribute a ON ("
> "\n a.attrelid=c.oid AND"
> "\n a.attnum=d.refobjsubid)"
> "\nWHERE d.classid='pg_catalog.pg_class'::pg_catalog.regclass"
> "\n AND d.refclassid='pg_catalog.pg_class'::pg_catalog.regclass"
> "\n AND d.objid=%s"
> "\n AND d.deptype='a'",
> oid);

Oh, OK. Yeah, that's kind of hairy.

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

In response to

Browse pgsql-hackers by date

  From Date Subject
Next Message Bruce Momjian 2014-09-03 15:01:12 Re: PL/pgSQL 2
Previous Message Robert Haas 2014-09-03 14:59:17 Re: Misleading error message in logical decoding for binary plugins