pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedConstraint doesn't see a currently insertet record
3+ messages / 2 participants
[nested] [flat]
* Constraint doesn't see a currently insertet record
@ 2005-02-11 18:10 KÖPFERL Robert <robert.koepferl@sonorys.at>
2005-02-13 01:57 ` Re: Constraint doesn't see a currently insertet record Michael Fuhr <mike@fuhr.org>
0 siblings, 1 reply; 3+ messages in thread
From: KÖPFERL Robert @ 2005-02-11 18:10 UTC (permalink / raw)
To: ; +Cc: pgsql-sql
Hi,
yes that's my problem.
I've got a table and I put lots of contraints on it so that data stays
consistent. One constraint calls a fcn to do some kind of count() over that
table but it omits the 'to be inserted record'. What to do?
concrete problem:
Suppose a table
i | tel | status | ...
..| \d+ | 1-7 | ...
status can be active, deleted or some more values
There may be not two rows where tel is equal and status is not deleted.
Other: As long as status=deleted there may be duplicate tel columns.
How would I make that sure?
My try was to create a check constraint like
("status" = 7) OR (("status" <> 7) AND (num_of_equal_tel_status_not_7("tel")
<= 1))
where num_of_equal_tel_status_not_7 is:
SELECT count(*) FROM "this table" WHERE "tel"=$1 AND "status"<>7
Note: status=7 means deleted
This works well until two recoreds are inserted which are status<>7.
The second gets inserted because the new record is not yet visible.
Another Idea was to make a trigger. But BTW how do I access a trigger
parameter if my trigger function must not have any parameter??????????
Ideas?
Tahks for any
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Constraint doesn't see a currently insertet record
2005-02-11 18:10 Constraint doesn't see a currently insertet record KÖPFERL Robert <robert.koepferl@sonorys.at>
@ 2005-02-13 01:57 ` Michael Fuhr <mike@fuhr.org>
0 siblings, 0 replies; 3+ messages in thread
From: Michael Fuhr @ 2005-02-13 01:57 UTC (permalink / raw)
To: KÖPFERL Robert <robert.koepferl@sonorys.at>; +Cc: pgsql-sql
On Fri, Feb 11, 2005 at 07:10:50PM +0100, KÖPFERL Robert wrote:
> Another Idea was to make a trigger. But BTW how do I access a trigger
> parameter if my trigger function must not have any parameter??????????
PL/pgSQL triggers can access arguments via TG_ARGV.
http://www.postgresql.org/docs/8.0/static/plpgsql-trigger.html
--
Michael Fuhr
http://www.fuhr.org/~mfuhr/
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Constraint doesn't see a currently insertet record
@ 2005-02-15 12:40 KÖPFERL Robert <robert.koepferl@sonorys.at>
0 siblings, 0 replies; 3+ messages in thread
From: KÖPFERL Robert @ 2005-02-15 12:40 UTC (permalink / raw)
To: ; +Cc: pgsql-sql
Thanks.
I managed it via a trigger.
> -----Original Message-----
> From: Michael Fuhr [mailto:mike@fuhr.org]
> Sent: Sonntag, 13. Februar 2005 02:57
> To: KÖPFERL Robert
> Cc: pgsql-sql@postgresql.org
> Subject: Re: [SQL] Constraint doesn't see a currently insertet record
>
>
> On Fri, Feb 11, 2005 at 07:10:50PM +0100, KÖPFERL Robert wrote:
>
> > Another Idea was to make a trigger. But BTW how do I access
> a trigger
> > parameter if my trigger function must not have any
> parameter??????????
>
> PL/pgSQL triggers can access arguments via TG_ARGV.
>
> http://www.postgresql.org/docs/8.0/static/plpgsql-trigger.html
>
> --
> Michael Fuhr
> http://www.fuhr.org/~mfuhr/
>
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2005-02-15 12:40 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2005-02-11 18:10 Constraint doesn't see a currently insertet record KÖPFERL Robert <robert.koepferl@sonorys.at>
2005-02-13 01:57 ` Michael Fuhr <mike@fuhr.org>
2005-02-15 12:40 Re: Constraint doesn't see a currently insertet record KÖPFERL Robert <robert.koepferl@sonorys.at>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox