current community

your communities

more stack exchange communities

Stack Exchange
tour help
careers 2.0
Take the 2-minute tour ×
Stack Overflow is a question and answer site for professional and enthusiast programmers. It's 100% free, no registration required.
up vote 0 down vote favorite

Ive got the following problem. I want to change some values of INSERT statement, e.g. there's a query:

INSERT INTO table(a,b) values(x,y);

But table has also another column c. I want to check (before INSERT) value of b from the query above and depending on one's value then set c=z but only for this specific row I've just inserted.

I think that some trigger can help in my problem but I've no idea how to do that. Any pseudocode would be appreciated.

share|improve this question

2 Answers 2

up vote 1 down vote accepted

It is the goog practice to restrict direct access to the table(s) for regular users. It can be done by using stored procedures and gives access rights only to them. For example (sorry for long code):

create table foo (
    a integer,
    b integer,
    c integer);

--== Create table. Yes, it is no PK, but for example it is not necesarry

create or replace function p_foo_insert(in aa integer, in ab integer) returns integer as
$$
declare
    result integer;
begin
    insert into foo (a, b, c) values (aa, ab, aa + ab) returning c into result;
    return result;
end; $$ language plpgsql;

--== It is function for row insertion. Inside this function you can do any data manipulation.

select p_foo_insert(1, 2);

--== Test statement. Result must be a + b = 1 + 2 = 3

So, forget about insert into, use stored procedures :o)

share|improve this answer
    
Great! Thanks @Abelisto, that's what I need. BTW, I wrote some triggers before and I hope they also should work but latter mistaken query smashed what trigger actually did. I lost many hours on it, eh:) –  kozooh Jun 11 '13 at 19:21
    
@kozooh Hope my answer was helpful. BTW I see that you tag your question as "postgresql-8.4". I not remember exactly, but in some 9.x brunch PG becomes faster atleast x2 times on data insertion. So, try to upgrade :o) –  Abelisto Jun 12 '13 at 2:07
up vote 1 down vote

You can do this in the INSERT statement itself, no trigger needed.

Given table t:

CREATE TEMP TABLE t (a int, b int, c int)

Default for c is NULL.
Consider this demo:

WITH i(x,y,z) AS (
    VALUES
     (1, 2, 111)
    ,(1, 7, 666)
    )
INSERT INTO t(a,b,c)
SELECT x, y, CASE WHEN y = 2 THEN z ELSE NULL END
FROM   i
RETURNING *

The WITH (requires Postgres 9.1+) and the RETURNING clause are just for testing convenience .
The important part is the SELECT with the CASE statement.

This test should work for pg 8.4:

INSERT INTO t(a,b,c)
SELECT x, y, CASE WHEN y = 2 THEN z ELSE NULL END
FROM   (
    VALUES
     (1, 2, 111)
    ,(1, 7, 666)
    ) i (x,y,z)
RETURNING *
share|improve this answer
    
Thank You for remind me about with clause. It is what is gem of PG and I always forget as Oracle programmer :o) But I not sure is this applicable to the source question. –  Abelisto Jun 11 '13 at 3:43
    
@Abelisto: The solution fits the question as given. A plpgsql function is probably overkill for this simple case. If you need a function wrap the CASE statement in an SQL function. –  Erwin Brandstetter Jun 11 '13 at 20:28
    
'A plpgsql function is probably overkill for this simple case.' - Yes, i agree. But it is our practice: restrict access to the DB objects for regular users/programmers except stored procedures. It is convenient, it is secure and it is helps to preserve the app-logic on the server side. –  Abelisto Jun 12 '13 at 2:26

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.