Re: [SQL] Last insert id
I'm new on postgreSQL, so this might not be the simplest sollution: Use a sequence instead of serial. After you have generated the new id with your_seq.nextval you can get thesame number again with your_seq.currval. details at http://www.postgresql.org/docs/7.3/static/functions-sequence.html mixo schrieb: >I have three tables which are related a serial field, table1_id, in on >of the tables. Updating the tables is done through a transaction. My >problem is, once I have insert a row in the first tables with >table1_id, I need for the other two tables. How can I get this? Is it >safe to use "select max(table1_id) from table1" after the insert? > >---(end of broadcast)--- >TIP 5: Have you checked our extensive FAQ? > > http://www.postgresql.org/docs/faqs/FAQ.html > ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] Last insert id
mixo wrote: I have three tables which are related a serial field, table1_id, in on of the tables. Updating the tables is done through a transaction. My problem is, once I have insert a row in the first tables with table1_id, I need for the other two tables. How can I get this? Is it safe to use "select max(table1_id) from table1" after the insert? A SERIAL column is just an ordinary integer with a default of nextval(). All you need to do is: INSERT INTO t1 (...) VALUES (...) INSERT INTO t2 (...) VALUES (currval()...) Sequences are guaranteed to give values relevant to your current connection. -- Richard Huxton Archonet Ltd ---(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
Re: [SQL] returning a recordset from PLpg/SQL
Terence Kearns wrote: Looking at the datatypes you can RETURN, it doesn't seem that there is a way to return a recordset I tried RETURNS SETOF RECORD but that doesn't work I even tried RETURNS SETOF fooTable%ROWTYPE What I would like to do is not that simple, I need to be able to build/declare a record definition with an arbitary list of columns and then return a set of that record which has it's values populated by queries and calculations inside the procedure. Declare your datatype (CREATE TYPE ...) and return a SETOF that. What you can't do is declare your datatype inside your function. Clearly PostgreSQL needs to know what type is being returned while parsing the function, so you can't have a "dynamic type" (if such an idea has any meaning at all). -- Richard Huxton Archonet Ltd ---(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
Re: [SQL] Triggers
Philip J. Boonzaaier wrote:
The technical reference gives an example of a trigger on a table - employee
Just to test this, I have created the following table,
CREATE TABLE employee
(name VARCHAR(30),
age int4,
state VARCHAR(2),
manager VARCHAR(3),
adult VARCHAR(3));
The I created a simple Function, as follows :
CREATE FUNCTION trig_insert_update_check_emp() RETURNS opaque AS '
The "opaque" type has been split into several different ones, if you're
using a recent version of PG, you'll want "RETURNS trigger".
[snip]
Now, when I execute the following :
INSERT INTO employee (name,age,state,manager)
VALUES ('sean',29,'tx','yes');
I get :
ERROR fmgr_info function 6264440 cache lookup failed
This usually means you redefined something - PG isn't smart enough (yet)
to re-check all the dependencies in something like this. I'd guess you
dropped/recreated the function after defining the trigger, yes?
The solution in older versions is to put the CREATE FUNCTION and CREATE
TRIGGER in the same file so that you drop/recreate both together.
HTH
--
Richard Huxton
Archonet Ltd
---(end of broadcast)---
TIP 7: don't forget to increase your free space map settings
[SQL] Schema + User-Defined Data Type Indexing problems...
PostgreSQL 7.4.2 -- All vacuumed and analyzed. I inserted the uniqueidentifier datatype into a new schema that I'm working on by changing the search_path to "my_schema" in the contrib SQL. It effectively created the datatype within the schema, all of its functions, operators, and operator classes. To move the data from the public schema into the new "my_schema" I had to create an assignment cast public.uniqueidentifier to my_schema.uniqueidentifier. I was profiling queries and I couldn't figure out why PostgreSQL wasn't using indexes. I'm having a heck of a time, and it seems like in my thrashing about to find a solution to this problem I have ruined the uniqueidentifier datatype in the schema... CREATE INDEX mt_uuid_idx ON my_schema.my_table USING btree (my_uuid); ERROR: data type my_schema.uniqueidentifier has no default operator class for access method "btree" HINT: You must specify an operator class for the index or define a default operator class for the data type. I can look at the operator classes and see that there is an operator class for btree for my_schema.uniqueidentifier. I must be doing something wrong with my schema set-up to have this much trouble with it. If this is the norm for complexity when using schema, I'm not sure it is worth the effort to impliment. Other PostgreSQL users are comfortable with the schema implimentation... I _must_ be doing something wrong. The bottom line for my problem is that searches that should be using indexes in the schema aren't. Please help me find out what's going on. CG __ Do you Yahoo!? Friends. Fun. Try the all-new Yahoo! Messenger. http://messenger.yahoo.com/ ---(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
[SQL] search and replace
I have a field in one of my tables that has a path to a file listed. I need to move those files and now have to update all those paths. Is there a simply search and replace type option in sql? or do I need to do an update using the entire new path name? If so, I think the easier solution will be to dump the table, use a macro to update it and then restore. Jodi -- ___ Jodi L Kanter BioInformatics Database Administrator University of Virginia (434) 924-2846 [EMAIL PROTECTED]
[SQL] Trigger problem
Hi guys Im tryint to make a trigger that marks a tuble as changed whenever someone has updated it my table looks something like this create table myTable ( ... changed boolean; ) now ive been working on a trigger and a sp that looks like this, but it doesnt work... create function myFunction returns trigger as ' begin new.changed = true; return new; end; ' language 'plpgsql'; create trigger myTrigger after update on lektioner for each row execute procedure myFunction(); the code compiles, runs, and doesnt whine about anything, but nothing changes... any ideas?? - Kasper ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [SQL] Trigger problem
kasper wrote: > Hi guys > > Im tryint to make a trigger that marks a tuble as changed whenever someone > has updated it > > my table looks something like this > > create table myTable ( > ... > changed boolean; > ) > > now ive been working on a trigger and a sp that looks like this, but it > doesnt work... > > create function myFunction returns trigger as ' > begin > new.changed = true; The line above is using the SQL equaliy opperator, you want the assignment operator: := as in new.changed := true; > return new; > end; > ' language 'plpgsql'; > > create trigger myTrigger > after update on lektioner > for each row > execute procedure myFunction(); > > > the code compiles, runs, and doesnt whine about anything, but nothing > changes... > > any ideas?? > > - Kasper -miker ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [SQL] Trigger problem
On Tue, 8 Jun 2004, kasper wrote: > Im tryint to make a trigger that marks a tuble as changed whenever someone > has updated it > > my table looks something like this > > create table myTable ( > ... > changed boolean; > ) > > now ive been working on a trigger and a sp that looks like this, but it > doesnt work... > > create function myFunction returns trigger as ' > begin > new.changed = true; > return new; > end; > ' language 'plpgsql'; > > create trigger myTrigger > after update on lektioner > for each row > execute procedure myFunction(); You want a before update trigger if you want to update the new row like that. ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [SQL] Trigger problem
On 6/8/2004 2:57 PM, Mike Rylander wrote: kasper wrote: Hi guys Im tryint to make a trigger that marks a tuble as changed whenever someone has updated it my table looks something like this create table myTable ( ... changed boolean; ) now ive been working on a trigger and a sp that looks like this, but it doesnt work... create function myFunction returns trigger as ' begin new.changed = true; The line above is using the SQL equaliy opperator, you want the assignment operator: := as in new.changed := true; PL/pgSQL accepts both. What's wrong is that it's an AFTER trigger, which is fired AFTER the new row is already stored on disk and thus cannot change it any more. Jan return new; end; ' language 'plpgsql'; create trigger myTrigger after update on lektioner for each row execute procedure myFunction(); the code compiles, runs, and doesnt whine about anything, but nothing changes... any ideas?? - Kasper -miker ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings -- #==# # It's easier to get forgiveness for being wrong than for being right. # # Let's break this rule - forgive me. # #== [EMAIL PROTECTED] # ---(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
Re: [SQL] returning a recordset from PLpg/SQL
Stephan Szabo wrote: As a starting point, SETOF "RECORD" is different from SETOF RECORD given PostgreSQL's fold case to lower case for unquoted names. Ahh! That will help :) That's what you get when you use a silly IDE instead of a regular editor like vi or notepad or something. Because I haven't written a procedure for years (and even then, I only wrote one or two simple ones) I'd forgotten the syntax so I thought using a dedicated prostgres procedure editor would make it easier. I didn't even think to considder the double quotes! Thanks for pointing that out. ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [SQL] Schema + User-Defined Data Type Indexing problems...
Chris Gamache <[EMAIL PROTECTED]> writes: > I'm having a heck of a time, and it seems like in my thrashing about > to find a solution to this problem I have ruined the uniqueidentifier > datatype in the schema... > CREATE INDEX mt_uuid_idx > ON my_schema.my_table USING btree (my_uuid); > ERROR: data type my_schema.uniqueidentifier has no default operator class for > access method "btree" > HINT: You must specify an operator class for the index or define a default > operator class for the data type. > I can look at the operator classes and see that there is an operator class for > btree for my_schema.uniqueidentifier. IIRC, the opclass has to be in a schema that is in your schema search path to be found by CREATE INDEX by default. If it isn't, you could specify it explicitly: CREATE INDEX mt_uuid_idx ON my_schema.my_table USING btree (my_uuid USING my_schema.uuidopclass); It's possible that we could think of a more convenient behavior for default opclasses, but I don't want to do something that would foreclose having similarly-named datatypes in different schemas. You have any suggestions? regards, tom lane ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [SQL] What's wrong with my date/interval arithmetic?
Wojtek <[EMAIL PROTECTED]> writes:
> Investigating that a little further I found out that there is a difference
> in results returned by age:
> select age(cast(to_timestamp('2003-12-01 03:50:45','-MM-dd HH24:MI:SS') as
> timestamp),
> cast(to_timestamp('2003-10-17 23:07:00','-MM-dd HH24:MI:SS') as
> timestamp))
> is '1 mon 14 days 04:43:45'
> and
> select age(to_timestamp('2003-12-01 03:50:45','-MM-dd HH24:MI:SS'),
> to_timestamp('2003-10-17 23:07:00','-MM-dd HH24:MI:SS'))
> is '1 mon 13 days 04:43:45'
I get '1 mon 14 days 04:43:45' and '1 mon 14 days 05:43:45'
respectively. This is a reasonable result for my timezone (EST5EDT),
because there is a daylight-savings transition involved:
regression=# select to_timestamp('2003-12-01 03:50:45','-MM-dd HH24:MI:SS');
to_timestamp
2003-12-01 03:50:45-05
(1 row)
regression=# select to_timestamp('2003-10-17 23:07:00','-MM-dd HH24:MI:SS');
to_timestamp
2003-10-17 23:07:00-04
(1 row)
Note the October date is taken as GMT-4, the December GMT-5. The hour
gained in the fall DST transition is accounted for when doing
timezone-aware arithmetic, but not when doing timezone-free arithmetic.
I still think the behavior you see is related to the timezone you're
using, which you still haven't told us. Also, what PG version are you
running, and on what platform?
regards, tom lane
---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] Getting FK relationships from information_schema
Peter Eisentraut <[EMAIL PROTECTED]> writes: > Tom Lane wrote: >> We have discussed changing the default names of FK constraints >> before. I have no problem with doing something like the above --- any >> objection out there? > I think it's a good idea. It will also make the error messages of the > kind "foreign key $1 violated" a bit clearer by default. Right ho --- I'm working on a patch that will choose default names of the form table_column_checkN and table_column_fkeyN, similar to what we already do for index constraints. regards, tom lane ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
