Re: derive the sequence name of a column - Mailing list pgsql-novice

From Mel Jamero
Subject Re: derive the sequence name of a column
Date
Msg-id 004c01c39eb4$3d73d890$1b06a8c0@cmpmel
Whole thread Raw
In response to Re: derive the sequence name of a column  (Bruno Wolff III <bruno@wolff.to>)
Responses Re: derive the sequence name of a column
List pgsql-novice
Thank for the reply Bruno but I need more. =)

Sorry, I have to send this again because I haven't figured out how to
solve this.

Can anyone please tell me exactly how the name of a sequence a field is
using (manually created or generated by a serial) could be derived
programmatically (using libpq or through SQL)?

Thus:

CREATE SEQUENCE an_unknown_sequence_name;
CREATE TABLE test (
  test_id   integer default nextval('an_unknown_sequence_name'),
  useless_redundant_test_id serial
);

How do I programmatically extract that column 'test_id' in table 'test'
is using 'an_unknown_sequence_name'

TIA!

Mel


-----Original Message-----
From: pgsql-novice-owner@postgresql.org
[mailto:pgsql-novice-owner@postgresql.org] On Behalf Of Bruno Wolff III
Sent: Monday, October 27, 2003 11:45 AM
To: Mel Jamero
Cc: pgsql-novice@postgresql.org
Subject: Re: [NOVICE] derive the sequence name of a column

On Fri, Oct 24, 2003 at 15:41:00 +0800,
  Mel Jamero <mel@gmanmi.tv> wrote:
> Hi!

Please don't reply to messages to start new threads.

>
> Can anyone please tell me exactly how the name of a sequence a field
is
> using (manually created or generated by a serial) could be derived
> programmatically (using libpq or through SQL)?

When creating a sequence for a serial type, for short names the format
is
tablename underline columnname underline seq . If the resulting name is
longer
than 63 characters then something else is used so as to get a shorter
name.
If you have control over the table and column names than you can make
sure the
names are always short enough so that the simple algorithm is used.

For manually created references to sequences, you will probably need to
parse the default value. I don't know how to do this, but using the
-E on pgsql and using \d sampletable should show you what query to use.

>
> Thus:
>
> CREATE SEQUENCE an_unknown_sequence_name;
> CREATE TABLE test (
>   test_id   integer default nextval('an_unknown_sequence_name'),
>   useless_redundant_test_id serial
> );
>
> How do I programmatically extract that column 'test_id' in table
'test'
> is using 'an_unknown_sequence_name'

---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster


pgsql-novice by date:

Previous
From: Tom Lane
Date:
Subject: Re: Capturing vacuum output
Next
From: Ennio-Sr
Date:
Subject: [OT] Unable to access PostgreSQL mailing list archive