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.
--
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]
