[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 ' DECLARE var VARCHAR; BEGIN var := TG_ARGV[0] NEW. := ''whatever''; RETURN NEW; END; ' LANGUAGE 'plpgsql' ; The aim of this uninteresting function is to assign the value 'whatever' to the table column that is passed in by the calling trigger as TG_ARGV[0], i.e. the first calling argument. What I don't know is what to put into the dot notation in place of "." so that the column of NEW that is addressed by the assignment statement is the one passed in as the first argument. Is there any PL/pgSQL construct that could be substituted in here to achieve this result? If not, can anybody suggest a way to write a trigger-called function that would accomplish the same result? In case it's not obvious, the underlying goal is to write a single trigger-called function that could modify different columns for each trigger that called it, where each trigger specified the target column by a calling argument (or by any other viable mechanism). ~ TIA ~ Ken ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
[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-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 ' DECLARE var VARCHAR; BEGIN var := TG_ARGV[0] NEW. := ''whatever''; RETURN NEW; END; ' LANGUAGE 'plpgsql' ; The aim of this uninteresting function is to assign the value 'whatever' to the table column that is passed in by the calling trigger as TG_ARGV[0], i.e. the first calling argument. What I don't know is what to put into the dot notation in place of "." so that the column of NEW that is addressed by the assignment statement is the one passed in as the first argument. Is there any PL/pgSQL construct that could be substituted in here to achieve this result? If not, can anybody suggest a way to write a trigger-called function that would accomplish the same result? In case it's not obvious, the underlying goal is to write a single trigger-called function that could modify different columns for each trigger that called it, where each trigger specified the target column by a calling argument (or by any other viable mechanism). ~ TIA ~ Ken
[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 DDL, it interprets the ‘now’ as the timestamp when the table is created, so that the tables definition reads as if the DDL were: effective_date_and_time TIMESTAMP WITH TIME ZONE not null default ' 2005-12-14 11:00:16.749616-06 ', so all of the newly inserted rows get assigned effective_date_and_time = ' 2005-12-14 11:00:16.749616-06 ', which in addition to being wrong leads to uniqueness constraint violations. ~ TIA ~ Ken
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] Defaulting a column to 'now' > > "Ken Winter" <[EMAIL PROTECTED]> writes: > > How can a column's default be set to 'now', meaning 'now' as of when > each > > row is inserted? > > You need a function, not a literal constant. The SQL-spec way is > CURRENT_TIMESTAMP > (which is a function, despite the spec's weird idea that it should be > spelled without parentheses); the traditional Postgres way is > now() > > Either way only sets an insertion default, though. If you want to > enforce a correct value on insertion, or change the value when the > row is UPDATEd, you need to use a trigger. > > regards, tom lane ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
[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_TIMESTAMP,
expiration_date_and_time TIMESTAMP WITH TIME ZONE null default
'infinity',
user_name VARCHAR(255)null,
constraint PK_MY_DATA primary key
(effective_date_and_time, id)
);
I have a view, my_data_now, defined as:
SELECT
my_data.id,
my_data.user_name,
my_data.effective_date_and_time,
my_data.expiration_date_and_time
FROMmy_data
WHERE my_data.effective_date_and_time <= 'now'::text::timestamp(6)
with time zone
AND my_data.expiration_date_and_time >=
'now'::text::timestamp(6) with time zone;
And I have this rule (among others):
CREATE OR REPLACE RULE upd_my_data_now AS
ON UPDATE TO my_data_now
DO INSTEAD
(
/* Update current record, and make it effective now. */
UPDATE my_data
SET id = NEW.id,
user_name = NEW.user_name,
effective_date_and_time =
('now'::text)::timestamp(6) with time zone
WHERE effective_date_and_time =
OLD.effective_date_and_time
AND id = OLD.id;
/* Insert a record containing the old values,
and expire it as of now. */
INSERT INTO my_data (
effective_date_and_time,
expiration_date_and_time,
id,
user_name)
VALUES (
OLD.effective_date_and_time,
('now'::text)::timestamp(6) with time zone,
OLD.id,
OLD.user_name)
)
;
This rule is supposed to (1) cause an update directed to the view
"my_data_now" to be made to the underlying table "my_data", (2) reset the
"effective_date_and_time" of that row to 'now', (3) insert a record
containing the old values into "my_data", and (4) expire that "old" record
by setting its "expiration_date_and_time" to 'now'.
But when I try to do an update against the view "my_data_now" with a query
such as:
update my_data_now set user_name = 'Suzy' where id = 1;
I get:
ERROR: duplicate key violates unique constraint "pk_my_data"
Presumably this happens when the rule tries to insert the new row. The new
row does indeed contain the "old" id and effective_date_and_time. However,
the rule is structured so that the current row's "effective_date_and_time"
gets updated to 'now' *before* the new row is inserted, making its value
different from the old "effective_date_and_time". So the uniqueness
conflict shouldn't occur.
I figure either there's some bug in my code that I can't see, or else the
PostgreSQL rule processor works in some way that I don't understand.
In either case, help!
~ TIA
~ Ken
---(end of broadcast)---
TIP 4: Have you searched our list archives?
http://archives.postgresql.org
Re: [SQL] Rule causes baffling error
Richard ~ Thanks for your response. Can a trigger be written on a *view*? I can't find anything in the PostgreSQL docs that answers this question. I originally wrote these actions (described in my original message) as a trigger on my base table, but then realized I was getting in deeper and deeper trouble because (a) I was getting into cascading triggers that I didn't want and (b) I need to enable some queries to access the base table without triggering these actions. That's why I set up the view, and then I assumed that the only way I could implement these actions 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: > > This rule is supposed to (1) cause an update directed to the view > > "my_data_now" to be made to the underlying table "my_data", (2) reset > the > > "effective_date_and_time" of that row to 'now', (3) insert a record > > containing the old values into "my_data", and (4) expire that "old" > record > > by setting its "expiration_date_and_time" to 'now'. > > I think you want a trigger rather than a rule. > > Rules rewrite the query structure, triggers let you deal with values on > a row-by-row basis (for row-level triggers). > > -- >Richard Huxton >Archonet Ltd ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [SQL] Rule causes baffling error
elevant change is that I'm now expiring the record with the old data and inserting the one with the new data, rather than vice versa. I still don't know why the old rule didn't work and this one does, but hey, whatever. Another advantage of the new one is that I don't have to re-point foreign keys that were already pointed to the record containing the old data, because 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.) Thanks for your help. I hope this little essay is of some value to others. ~ Ken > -Original Message- > From: Richard Huxton [mailto:[EMAIL PROTECTED] > Sent: Monday, December 19, 2005 11:05 AM > To: Ken Winter > Cc: 'PostgreSQL pg-sql list' > Subject: Re: [SQL] Rule causes baffling error > > Ken Winter wrote: > > Richard ~ > > > > Thanks for your response. > > > > Can a trigger be written on a *view*? I can't find anything in the > > PostgreSQL docs that answers this question. > > There's nothing for them to fire against even if you could attach the > trigger. I suppose you could have a statement-level trigger in more > recent versions, but for row-level triggers there aren't any rows in the > view to be affected. > > > I originally wrote these actions (described in my original message) as a > > trigger on my base table, but then realized I was getting in deeper and > > deeper trouble because (a) I was getting into cascading triggers that I > > didn't want and (b) I need to enable some queries to access the base > table > > without triggering these actions. That's why I set up the view, and > then I > > assumed that the only way I could implement these actions was as rules. > > Hmm - the cascading should be straightforward enough to deal with. When > you are updating check if NEW.expiration_date_and_time = now() and if so > exit the trigger function (since there's nothing to do anyway). > > The other thing you might want to consider is whether the "live" data > should be in the same table as the "old" data. That will depend on how > you want to use it - conceptually is it all one continuum or is the > "old" data just for archive purposes. > > Now, having got this feature working, why do you want to bypass it? Will > it be a specific user, involve specific patterns of values or what? > > -- >Richard Huxton >Archonet Ltd ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
[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 info: the Information Schema and in the PG Catalog. Which source is preferable? Or if that answer isn’t absolute, what are the reasons or conditions for preferring one over the other? Also, a specific question: Does the Information Schema offer any way to list the sequences that exist and their attributes? I can’t seem to find any. ~ TIA ~ Ken
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 Information Schema vs the PG Catalog > > I would say that pg_catalog is the more complete one whereas the > information_schema the more generic, standards-conformant place. I would > stick with the information_schema unless that becomes inadequate. A case > in point may be sequences. Apart from > information_schema.columns.column_default I haven't seen them > represented anywhere there (please someone correct me if I am wrong). > You can get more information about sequences from pg_catalog.pg_class > (look for pg_class.relkind='S') and various views that sit on top of > that (e.g. pg_statio_all_sequences). > > George > > > > 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 info: the Information Schema and in the > > PG Catalog. > > > > Which source is preferable? Or if that answer isn't absolute, > > what are the reasons or conditions for preferring one over the > > other? > > > > Also, a specific question: Does the Information Schema offer any > > way to list the sequences that exist and their attributes? I > > can't seem to find any. > > ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
[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 array local to a PL/pgSQL function. I tried the following guess, but it only won me a “syntax error at or near VARCHAR: DECLARE my_array VARCHAR []; … ~ TIA ~ Ken
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: Ken Winter
> Cc: PostgreSQL pg-sql list
> Subject: Re: [SQL] Arrays in PL/pgSQL routines?
>
> Ken Winter wrote:
> > Can arrays be declared in PL/pgSQL routines? If so, how?
> >
>
> > DECLARE
> >
> try:
> my_array VARCHAR[] := '{}';
>
> not sure if this works in 7.4 though, if that's the version that you are
> using.
---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster
[SQL] Still struggling with history tables
Friends ~
I'm still trying to implement a solution to the requirement to keep a
complete history of data changes to a "person" table. (See earlier
correspondence below.) I'm trying for a variant of the architecture
suggested by Richard Huxton (also below). In my variant, I have this
"identifier" table, carrying the id and invariant info about each person:
/*==*/
/* Table: person_i */
/*==*/
create table person_i (
idi BIGSERIALnot null,
date_of_birthDATE null,
constraint PK_person_i_key_1 primary key (idi)
)
;
And then I have this "history" table, carrying the time-varying info on
which I want to keep a complete history:
/*==*/
/* Table: person_h */
/*==*/
create table person_h (
idh INT8 not null,
start_date DATE not null default 'now()',
end_date DATE null,
name VARCHAR(255) null,
constraint PK_person_h_key_1 primary key (idh, start_date),
constraint fk_reference_6 foreign key (idh)
references person_i (idi)
on delete restrict on update restrict
)
;
Triggers are in place on the "person_h" table so that when an app does an
update, the current h record is expired (with its old data) and a new record
(wuth the updated data)is inserted and made effective "now". What I'm now
trying to build is this view:
/*==*/
/* View: person */
/*==*/
create view person as
select idi, date_of_birth, start_date, end_date, name
from person_i i, person_h h
where i.idi = h.idh;
I want to enable users (and apps) who need to deal only with current data to
be able to treat "person" as a real table (i.e. to write to it as well as
read from it). Specifically, the requirements are that when a user does:
. Insert - The system inserts a record into the i table and the first
record in the h table.
. Select - The system returns attributes of i and h tables (not
duplicating the identifier columns).
. Update - The system allows updating of i attributes
(update-in-place, not creating a new history record) and h attributes
(creating a new history record).
. Delete - The system deletes the i record and all of its h records.
I'm stuck on how to implement the "insert" action, which I thought would be
simple. The problem is this: The i table id is of type BIGSERIAL, i.e.
sequence-assigned. I've tried writing the following rule to get both the i
record and the first h record inserted:
CREATE RULE ru AS
ON INSERT TO person
DO INSTEAD (
INSERT INTO person_i DEFAULT VALUES;
INSERT INTO person_h (idh) VALUES (NEW.idi)
);
I thought this would grab the sequence-assigned value of person_i.idi to put
into person_h.idh (this trick works in trigger functions), but instead it
just assigns Null to person_h.idh, and the transaction fails with a "not
null" violation on person_h.idh. And now that I look at the documentation
(http://www.postgresql.org/docs/7.4/static/rules-update.html), it says that
a column not assigned in the invoking query "is replaced by a null value
(for an INSERT)" in the NEW pseudo-record. Bummer. Triggers behave nicely,
but rules don't.
I'd be willing to do it with a trigger function instead, but I can'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?
~ TIA
~ Ken
> -Original Message-
> From: Richard Huxton [mailto:[EMAIL PROTECTED]
> Sent: Tuesday, December 20, 2005 4:16 AM
> To: Ken Winter
> Cc: 'PostgreSQL pg-sql list'
> Subject: Re: [SQL] Rule causes baffling error
>
> Ken Winter wrote:
> > Richard ~
> >
> > Let me zoom out for a moment, for the bigger picture.
> >
> > As you have inferred, what I'm trying to do is develop a history-
> preserving
> > table ("my_data" in the example that started this thread). *Most* user
> > programs would see and manipulate this table as if it contained only the
> > current rows (marked by effective_date_and_time <= 'now' and
> > expiration_date_and_time = 'infinit
