Thread: libpq

libpq

From

"creid"

Date:

23 October 2003, 19:17:53

Can anyone explain to me how I can successfully fetch a date column defined
in a 7.3 table into a c variable and have it store the date just as it is in
the table?  platform linux, gnu C, pgsql 7.3


Thanks
C

Re: libpq

From

Bruno LEVEQUE

Date:

23 October 2003, 20:23:57


creid wrote:

>Can anyone explain to me how I can successfully fetch a date column defined
>in a 7.3 table into a c variable and have it store the date just as it is in
>the table?  platform linux, gnu C, pgsql 7.3
>
>
>
>

 From C to Postgres :


time_t                date_jour;
struct tm             *date_lisible;
time(&date_jour);
date_lisible = localtime(&date_jour);

sprintf(recherche,"DECLARE portal CURSOR FOR select lib from Thetable
where date = '01/%d/%d'::date and ....",
date_lisible->tm_mon+1,1900+date_lisible->tm_year);


res1 = PQexec(connexion,recherche);
PQclear(res1);

res1 = PQexec(connexion,"FETCH ALL in portal1");

Form Postgres to C:

use "declare portal" and not "declare portal binary" and ypu mut extract
the date like you want.

Bruno


>Thanks
>C
>
>
>---------------------------(end of broadcast)---------------------------
>TIP 3: if posting/reading through Usenet, please send an appropriate
>      subscribe-nomail command to majordomo@postgresql.org so that your
>      message can get through to the mailing list cleanly
>
>
>

--
Bruno LEVEQUE
System Engineer
SARL NET6D
bruno.leveque@net6d.com
http://www.net6d.com

derive the sequence name of a column

From

"Mel Jamero"

Date:

24 October 2003, 07:09:47

Hi!

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

Re: derive the sequence name of a column

From

Bruno Wolff III

Date:

27 October 2003, 02:38:57

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'

Re: derive the sequence name of a column

From

"Mel Jamero"

Date:

30 October 2003, 05:35:57

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

Re: derive the sequence name of a column

From

Stephan Szabo

Date:

30 October 2003, 14:18:24

On Thu, 30 Oct 2003, Mel Jamero wrote:

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

I'd suggest looking in pg_attrdef.

Something like:

select pg_attrdef.* from pg_attrdef, pg_namespace, pg_class, pg_attribute
where pg_namespace.nspname='public' and
pg_class.relnamespace=pg_namespace.oid and pg_class.relname='test' and
pg_attribute.attrelid=pg_class.oid and pg_attribute.attname='test_id' and
pg_attrdef.adrelid=pg_class.oid and pg_attrdef.adnum=pg_attribute.attnum;

(filling in the schema, table name and column name for the constants).

Re: derive the sequence name of a column

From

"Mel Jamero"

Date:

31 October 2003, 06:08:52

Works fine with 7.2.2.  Thanks!  I still can't figure it out in 7.1.3

There's no pg_namespace there.  Any ideas?

Thanks again,

MEL

-----Original Message-----
From: Stephan Szabo [mailto:sszabo@megazone.bigpanda.com]
Sent: Thursday, October 30, 2003 11:18 PM
To: Mel Jamero
Cc: 'Bruno Wolff III'; pgsql-novice@postgresql.org
Subject: Re: [NOVICE] derive the sequence name of a column


On Thu, 30 Oct 2003, Mel Jamero wrote:

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

I'd suggest looking in pg_attrdef.

Something like:

select pg_attrdef.* from pg_attrdef, pg_namespace, pg_class,
pg_attribute
where pg_namespace.nspname='public' and
pg_class.relnamespace=pg_namespace.oid and pg_class.relname='test' and
pg_attribute.attrelid=pg_class.oid and pg_attribute.attname='test_id'
and
pg_attrdef.adrelid=pg_class.oid and
pg_attrdef.adnum=pg_attribute.attnum;

(filling in the schema, table name and column name for the constants).

Re: derive the sequence name of a column

From

Stephan Szabo

Date:

31 October 2003, 14:51:51

On Fri, 31 Oct 2003, Mel Jamero wrote:

> Works fine with 7.2.2.  Thanks!  I still can't figure it out in 7.1.3
>
> There's no pg_namespace there.  Any ideas?

I think you can just remove the join with pg_namespace and the associated
where clauses (the nspname one and the join condition one).  I'm not
sure if there'll be any other problems since I don't currently have a
7.1.x system to try against.

And, for good measure, if you're running 7.1.x, you should really upgrade
soon. ;)

> select pg_attrdef.* from pg_attrdef, pg_namespace, pg_class,
> pg_attribute
> where pg_namespace.nspname='public' and
> pg_class.relnamespace=pg_namespace.oid and pg_class.relname='test' and
> pg_attribute.attrelid=pg_class.oid and pg_attribute.attname='test_id'
> and
> pg_attrdef.adrelid=pg_class.oid and
> pg_attrdef.adnum=pg_attribute.attnum;
>
> (filling in the schema, table name and column name for the constants).

---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
      subscribe-nomail command to majordomo@postgresql.org so that your
      message can get through to the mailing list cleanly

Re: derive the sequence name of a column

From

Stephan Szabo

Date:

31 October 2003, 18:52:23

On Fri, 31 Oct 2003, Mel Jamero wrote:

> Works fine with 7.2.2.  Thanks!  I still can't figure it out in 7.1.3
>
> There's no pg_namespace there.  Any ideas?

I think you can just remove the join with pg_namespace and the associated
where clauses (the nspname one and the join condition one).  I'm not
sure if there'll be any other problems since I don't currently have a
7.1.x system to try against.

And, for good measure, if you're running 7.1.x, you should really upgrade
soon. ;)

> select pg_attrdef.* from pg_attrdef, pg_namespace, pg_class,
> pg_attribute
> where pg_namespace.nspname='public' and
> pg_class.relnamespace=pg_namespace.oid and pg_class.relname='test' and
> pg_attribute.attrelid=pg_class.oid and pg_attribute.attname='test_id'
> and
> pg_attrdef.adrelid=pg_class.oid and
> pg_attrdef.adnum=pg_attribute.attnum;
>
> (filling in the schema, table name and column name for the constants).

---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
      subscribe-nomail command to majordomo@postgresql.org so that your
      message can get through to the mailing list cleanly