[SQL]

In PL/pgSQL, is there a way to put a *variable* column-name in a dot notation reference to a RECORD column? For example, suppose I want to write a function like the following, which is to be called by a "BEFORE INSERT" trigger: CREATE OR REPLACE FUNCTION foo ( ) RETURNS TRIGGER AS ' DEC

[SQL] Variable column names in PL/pgSQL RECORD referencces

(Sorry for the redundancy – I sent this query earlier but forgot to put a title on it.  Seems like it would be more useful with a title, so here it is again.  If there’s a moderator who can delete my earlier message, please do so.)   In PL/pgSQL, is there a way to put a *variable* column-

[SQL] Defaulting a column to 'now'

How can a column’s default be set to ‘now’, meaning ‘now’ as of when each row is inserted?   For example, here’s a snip of DDL:   create table personal_data (… effective_date_and_time TIMESTAMP WITH TIME ZONE not null default 'now',…   The problem is, when PostgreSQL processes this D

Re: [SQL] Defaulting a column to 'now'

Thanks, Tom (also Keith Worthington and Bricklen Anderson). That works. ~ Ken > -Original Message- > From: Tom Lane [mailto:[EMAIL PROTECTED] > Sent: Wednesday, December 14, 2005 1:15 PM > To: Ken Winter > Cc: PostgreSQL pg-sql list > Subject: Re: [SQL] Defaultin

[SQL] Rule causes baffling error

I'm trying to figure out why a rule gives me a uniqueness violation when I try to do an update. I have a table, "my_data", defined as: create table my_data ( id INT8 not null default nextval('person_seq'), effective_date_and_time TIMESTAMP WITH TIME ZONE not null default CURRENT_T

Re: [SQL] Rule causes baffling error

ns was as rules. ~ Ken > -Original Message- > From: Richard Huxton [mailto:[EMAIL PROTECTED] > Sent: Monday, December 19, 2005 4:08 AM > To: Ken Winter > Cc: 'PostgreSQL pg-sql list' > Subject: Re: [SQL] Rule causes baffling error > > Ken Winter wrote: > >

Re: [SQL] Rule causes baffling error

that record stays in place. (The other change, adding the lines AND effective_date_and_time <= CURRENT_TIMESTAMP AND expiration_date_and_time >= CURRENT_TIMESTAMP; to the UPDATE, was necessary to keep updates to the "my_data_now" from updating the expired rows as well.) Tha

[SQL] The Information Schema vs the PG Catalog

I’m writing PL/pgSQL routines that generate triggers, functions, and rules based on design characteristics of tables, columns, and other database objects.  These routines need to be able to look up the definitions of these objects.  I see that there are two places available to look up this

Re: [SQL] The Information Schema vs the PG Catalog

Thanks, George. What you say fits with what I was finding. I think that's the way I will go. ~ Ken > -Original Message- > From: George Pavlov [mailto:[EMAIL PROTECTED] > Sent: Tuesday, December 27, 2005 12:11 PM > To: pgsql-sql@postgresql.org > Cc: [EMAIL PROTECTED] > Subject: Re: The I

[SQL] Arrays in PL/pgSQL routines?

Can arrays be declared in PL/pgSQL routines?  If so, how?   Section 8.10 of the documentation (http://www.postgresql.org/docs/7.4/static/arrays.html) tells how to declare and use arrays as table columns.  But I don’t find any part of the documentation that says how to declare a simple arr

Re: [SQL] Arrays in PL/pgSQL routines?

Bricklen ~ That works. (Odd that the initialization seems to be necessary to make it work.) Thanks! Yes, I'm using version 7.4. ~ Ken > -Original Message- > From: Bricklen Anderson [mailto:[EMAIL PROTECTED] > Sent: Thursday, December 29, 2005 12:53 PM > To

[SQL] Still struggling with history tables

;t attach a trigger to a view. I considered doing it with a trigger function on the person_i table, but I don't know how that could be made to cause an insert of the person_h table record - and the assignment of h table values such as "name" from the app's query. Suggestions?