Hi pgsql-general,
(all examples are pseudo-code)
We really love PostgreSQL, it's getting better and better, there is just
one thing, something that has always led to some dislike: OID's
I understand why they did it and all, but still.
To make life easier, it's always good to find a general way of doing things.
But sometimes it just takes a lot more time and effort to find something
you feel even mildly comvertable with.
This is one of those times.
Some people use this way of getting the real insertID:
insert into whatever (text) values ('something');
oid = insertID ();
select id from whatever where whatever.oid = oid;
you get the general idea.
But OID's are optional now... so, not terrible great.
Or with the use of PG's nextval () (which is the preferred/intended
PostgreSQL-way and I agree):
id = nextval ("whatever_id_seq");
insert into whatever (id, text) values (id, 'something');
Something that works always... better, but you need to know the name of
the sequence, bummer.
So we constructed this query:
SELECT
pg_attrdef.adsrc
FROM
pg_attrdef,
pg_class,
pg_attribute
WHERE
pg_attrdef.adnum = pg_attribute.attnum
AND pg_attrdef.adrelid = pg_class.oid
AND pg_attribute.attrelid = pg_class.oid
AND pg_attribute.attname = 'id'
AND pg_class.relname = 'whatever'
(pg_class is a table that holds for instance table-names, etc.,
pg_attribute + pg_attrdef are table's with field-information)
it will result in the default-value of a field of a table..., which means
you get something like this:
nextval('whatever_id_seq'::text)
so, now you have the sequence..., or atleast a way to get to the nextval.
All you have to do is this:
SELECT nextval('whatever_id_seq'::text);
done.
So, now all you have to know is:
- table
- field with ID + default-value
- insert query
Well, maybe that's crazy too, but atleast it's something that'll work.
Probably not the best way, but it's a way.
We're just wondering what people think about such an approach.
Have a nice day,
Lennie.
PS This has been tested with:
- 6.5.3 (Debian Linux Package)
- 8.0 Beta 3 Win32 (msi-install)
_____________________________________
New things are always on the horizon.
---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match
