On Wednesday 09 January 2008 09:34:10 pm johnf wrote:
> On Wednesday 09 January 2008 09:15:00 pm Adrian Klaver wrote:
> > On Wednesday 09 January 2008 8:50 pm, johnf wrote:
> > > On Wednesday 09 January 2008 01:45:26 pm Adrian Klaver wrote:
> > > > I am trying to work through my own problems with new(). In doing so I
> > > > ran into a problem with the Postgres driver not finding the sequence
> > > > on my table. The details are below. I pulled the query from
> > > > getLastInsertID and ran with the result shown below, no relname.
> > > > Below that is the schema for the table and below that a query against
> > > > pg_class that shows that the sequence does exist.
> >
> > <snip>
> >
> > > > Thanks,
> > > > --
> > > > Adrian Klaver
> > >
> > > The problem is your data type - it is not a 'serial'. I have been
> > > researching but have not come up with a "select" to cover using other
> > > data types. If you find anything let me know.
> >
> > The key seems to be using the pg_attrdef table. It holds the default
> > values for table columns. Use the oid of the table to find
> > pg_attrdef.adrelid and the pg_attribute.attnum to find pg_attrdef.adnum.
> > pg_attrdef.adbin contains a binary representation of the default value
> > for the column. This can be made readable using system function
> > pg_get_expr(expr_text, relation_oid) where relation_oid is the table oid.
> > I haven't worked out a complete solution yet as it involves parsing out
> > the sequence name.
> >
> > Hope this helps,
>
> Exactly. I want the select to return the sequence name.
>
> Try this and see if you come up with any ideas
> SELECT c.conname AS constraint_name,
> CASE c.contype
> WHEN 'c' THEN 'CHECK'
> WHEN 'f' THEN 'FOREIGN KEY'
> WHEN 'p' THEN 'PRIMARY KEY'
> WHEN 'u' THEN 'UNIQUE'
> END AS "constraint_type",
> CASE WHEN c.condeferrable = 'f' THEN 0 ELSE 1 END AS
> is_deferrable, CASE WHEN c.condeferred = 'f' THEN 0 ELSE 1 END AS
> is_deferred, t.relname AS table_name,
> array_to_string(c.conkey, ' ') AS constraint_key,
> CASE confupdtype
> WHEN 'a' THEN 'NO ACTION'
> WHEN 'r' THEN 'RESTRICT'
> WHEN 'c' THEN 'CASCADE'
> WHEN 'n' THEN 'SET NULL'
> WHEN 'd' THEN 'SET DEFAULT'
> END AS on_update,
> CASE confdeltype
> WHEN 'a' THEN 'NO ACTION'
> WHEN 'r' THEN 'RESTRICT'
> WHEN 'c' THEN 'CASCADE'
> WHEN 'n' THEN 'SET NULL'
> WHEN 'd' THEN 'SET DEFAULT'
> END AS on_delete,
> CASE confmatchtype
> WHEN 'u' THEN 'UNSPECIFIED'
> WHEN 'f' THEN 'FULL'
> WHEN 'p' THEN 'PARTIAL'
> END AS match_type,
> t2.relname AS references_table,
> array_to_string(c.confkey, ' ') AS fk_constraint_key
> FROM pg_constraint c
> LEFT JOIN pg_class t ON c.conrelid = t.oid
> LEFT JOIN pg_class t2 ON c.confrelid = t2.oid
> WHERE t.relname = 'seqtest'
>
> seqtest should be any tablename
>
> But only give the constraint name and not the sequence name associated with
> the constraint name.
BTW below will give all the sequence names:
SELECT oid,relname
FROM pg_class
WHERE relkind = 'S'
AND relnamespace IN (
SELECT oid
FROM pg_namespace
WHERE nspname NOT LIKE 'pg_%'
AND nspname != 'information_schema'
);
--
John Fabiani
_______________________________________________
Post Messages to: [email protected]
Subscription Maintenance: http://leafe.com/mailman/listinfo/dabo-users
Searchable Archives: http://leafe.com/archives/search/dabo-users
This message: http://leafe.com/archives/byMID/dabo-users/[EMAIL PROTECTED]
