Re: Triggers using PL/pgSQL

От:

George Weaver <gweaver@shaw.ca>

Дата:

31 июля 2006 г. в 13:17:07

Re: Triggers using PL/pgSQL

От:

Ken Hill <ken@scottshill.com>

Дата:

31 июля 2006 г. в 13:02:34

Triggers using PL/pgSQL

От:

Thusitha Kodikara <kthusi@yahoo.com>

Дата:

31 июля 2006 г. в 01:17:03

Re: Triggers using PL/pgSQL

От:

Thusitha Kodikara <kthusi@yahoo.com>

Дата:

31 июля 2006 г. в 02:31:08

Hello,

Thanks a lot Aaron for the very quick and simple example. I just checked it on 7.4.5 also and it worked. I'll be able to continue with my development using the syntax of that example.

Regards,
-Thusitha

Aaron Bono <postgresql@aranya.com> wrote:

On 7/30/06, Thusitha Kodikara <kthusi@yahoo.com> wrote:
Hello,

I am interested in developing some triggers to keep track of records that are changed (possibly the changes of one or more specific columns). In addition to keeping the new values, I also need to keep the old values (may be on a separate table).

Though I have done similar things in other RDBMs using SQL, I find doing this in Postgres, a little bit complicated - may be because it needs to be done through a separate procedural language and through a separate function. The Postgres documentation also didn't provide much help ( the examples in C). I was mainly looking for example showing how to refer 'OLD' and 'NEW' rows using PL/pgSQL.

Can someone please direct me to some such examples?

How about this:

CREATE TABLE my_table (
   my_table_id BIGSERIAL NOT NULL,
   my_value VARCHAR(100) NOT NULL,
   CONSTRAINT my_table_pkey PRIMARY KEY (my_table_id)
);

CREATE TABLE my_table_history (
   my_table_id BIGINT NOT NULL,
   my_value VARCHAR(100) NOT NULL,
   create_dt TIMESTAMP NOT NULL,
   CONSTRAINT my_table_history_pkey PRIMARY KEY (my_table_id, create_dt)
);

CREATE OR REPLACE FUNCTION my_table_history_fn () RETURNS SETOF opaque AS
'
BEGIN
    -- if a trigger insert or update operation occurs
    IF TG_OP = ''INSERT'' OR TG_OP = ''UPDATE'' THEN
        execute
            ''INSERT INTO my_table_history ( '' ||
            ''    my_table_id, '' ||
            ''    my_value, '' ||
            ''    create_dt '' ||
            '') VALUES ( '' ||
            ''    '''''' || NEW.my_table_id || '''''', '' ||
            ''    '''''' || NEW.my_value || '''''', '' ||
            ''    now() '' ||
            '');''
        ;
        RETURN NEW;
    END IF;
END;
'
LANGUAGE 'plpgsql' VOLATILE CALLED ON NULL INPUT SECURITY INVOKER;

CREATE TRIGGER my_table_history_tr BEFORE UPDATE OR INSERT
    ON my_table FOR EACH ROW
    EXECUTE PROCEDURE my_table_history_fn();

I tried it out and it works in version 8.1.

==================================================================
   Aaron Bono
   Aranya Software Technologies, Inc.
   http://www.aranya.com
==================================================================

Re: Triggers using PL/pgSQL

От:

"Milen A. Radev" <milen@radev.net>

Дата:

31 июля 2006 г. в 13:13:10

PL/pgSQL and PHP 5

От:

PostgreSQL Admin <postgres@productivitymedia.com>

Дата:

9 августа 2006 г. в 23:36:40

I'm having this problem inserting data from my form using PL/pgSQL.  
Here is the simplified version of my table and function (this example 
does not work,  also ):

CREATE TABLE theirry.sample (
    staff_id serial PRIMARY KEY NOT NULL,
    firstname varchar(100),
    lastname varchar(150),
    username varchar(35),
    identifier varchar(40),
    address2 varchar(180),
    activated boolean,
    activated_keys varchar(32)
);

CREATE OR REPLACE FUNCTION insert_staff_b
    (insert_firstname varchar)
RETURNS VOID AS
$$
    DECLARE
    BEGIN       
            INSERT INTO theirry.sample
                (firstname)
            VALUES
                (insert_firstname);
        RETURN;
    END;
$$
LANGUAGE plpgsql;


I have a form with a value firstname then call the query in php

select insert_staff_b('$_POST['firstname']::varchar)


Still I get this error:
Warning: pg_query(): Query failed: ERROR: function 
insert_staff_b(character varying) does not exist HINT: No function 
matches the given name and argument types. You may need to add explicit 
type casts.

Suggestions or maybe a place to read up on this problem.

Thanks in advance,
J

Re: [PHP] PL/pgSQL and PHP 5

От:

PostgreSQL Admin <postgres@productivitymedia.com>

Дата:

10 августа 2006 г. в 00:42:54

Thanks for the catch. I've tried:

$connection->execute("SELECT 
insert_staff_b('$staff[insert_firstname]'::varchar)");
$connection->execute("SELECT 
insert_staff_b('".$staff['insert_firstname']."'::varchar)");

None work... I'm scratching my head on this one.

Thanks,
J

Re: PL/pgSQL and PHP 5

От:

PostgreSQL Admin <postgres@productivitymedia.com>

Дата:

10 августа 2006 г. в 00:52:54

Tom Lane wrote:
> PostgreSQL Admin  writes:
>   
>> CREATE OR REPLACE FUNCTION insert_staff_b
>>     (insert_firstname varchar)
>> RETURNS VOID AS
>> ...
>> Still I get this error:
>> Warning: pg_query(): Query failed: ERROR: function 
>> insert_staff_b(character varying) does not exist
>>     
>
> Sure looks like it oughta work.  One possibility is that you created the
> function in a schema that isn't part of the application's search path.
> Other than that, look for *really* silly errors, like not creating the
> function in the same database the application is connected to ...
>
> 			regards, tom lane
>
> ---------------------------(end of broadcast)---------------------------
> TIP 3: Have you checked our extensive FAQ?
>
>                http://www.postgresql.org/docs/faq
>
>   
I never thought of that.  I look into it.

Thanks,
J

Re: PL/pgSQL and PHP 5 - thanks

От:

PostgreSQL Admin <postgres@productivitymedia.com>

Дата:

10 августа 2006 г. в 01:01:41

Thanks,

The search path was the problem.  Sometimes it's the simple things.

Big thanks,
J

Re: Triggers using PL/pgSQL

От:

John DeSoi <desoi@pgedit.com>

Дата:

31 июля 2006 г. в 09:19:25

Re: PL/pgSQL and PHP 5

От:

Tom Lane <tgl@sss.pgh.pa.us>

Дата:

10 августа 2006 г. в 00:41:08

PostgreSQL Admin  writes:
> CREATE OR REPLACE FUNCTION insert_staff_b
>     (insert_firstname varchar)
> RETURNS VOID AS
> ...
> Still I get this error:
> Warning: pg_query(): Query failed: ERROR: function 
> insert_staff_b(character varying) does not exist

Sure looks like it oughta work.  One possibility is that you created the
function in a schema that isn't part of the application's search path.
Other than that, look for *really* silly errors, like not creating the
function in the same database the application is connected to ...

			regards, tom lane

Re: [PHP] PL/pgSQL and PHP 5

От:

John DeSoi <desoi@pgedit.com>

Дата:

10 августа 2006 г. в 00:25:52

On Aug 9, 2006, at 10:36 PM, PostgreSQL Admin wrote:

> select insert_staff_b('$_POST['firstname']::varchar)
>
>
> Still I get this error:
> Warning: pg_query(): Query failed: ERROR: function insert_staff_b 
> (character varying) does not exist HINT: No function matches the  
> given name and argument types. You may need to add explicit type  
> casts.

Your select statement above has unbalanced single quotes. Assuming  
this is not really the issue, I would check the search_path and look  
at the function in psql or some admin tool to make sure the function  
name does not have different capitalization.

John DeSoi, Ph.D.
http://pgedit.com/
Power Tools for PostgreSQL

Re: Triggers using PL/pgSQL

От:

"Aaron Bono" <postgresql@aranya.com>

Дата:

31 июля 2006 г. в 01:52:48

On 7/30/06, Thusitha Kodikara <kthusi@yahoo.com> wrote:

Hello,

I am interested in developing some triggers to keep track of records that are changed (possibly the changes of one or more specific columns). In addition to keeping the new values, I also need to keep the old values (may be on a separate table).

Though I have done similar things in other RDBMs using SQL, I find doing this in Postgres, a little bit complicated - may be because it needs to be done through a separate procedural language and through a separate function. The Postgres documentation also didn't provide much help ( the examples in C). I was mainly looking for example showing how to refer 'OLD' and 'NEW' rows using PL/pgSQL.

Can someone please direct me to some such examples?

How about this:

CREATE TABLE my_table (
   my_table_id BIGSERIAL NOT NULL,
   my_value VARCHAR(100) NOT NULL,
   CONSTRAINT my_table_pkey PRIMARY KEY (my_table_id)
);

CREATE TABLE my_table_history (
   my_table_id BIGINT NOT NULL,
   my_value VARCHAR(100) NOT NULL,
   create_dt TIMESTAMP NOT NULL,
   CONSTRAINT my_table_history_pkey PRIMARY KEY (my_table_id, create_dt)
);

CREATE OR REPLACE FUNCTION my_table_history_fn () RETURNS SETOF opaque AS
'
BEGIN
    -- if a trigger insert or update operation occurs
    IF TG_OP = ''INSERT'' OR TG_OP = ''UPDATE'' THEN
        execute
            ''INSERT INTO my_table_history ( '' ||
            ''    my_table_id, '' ||
            ''    my_value, '' ||
            ''    create_dt '' ||
            '') VALUES ( '' ||
            ''    '''''' || NEW.my_table_id || '''''', '' ||
            ''    '''''' || NEW.my_value || '''''', '' ||
            ''    now() '' ||
            '');''
        ;
        RETURN NEW;
    END IF;
END;
'
LANGUAGE 'plpgsql' VOLATILE CALLED ON NULL INPUT SECURITY INVOKER;

CREATE TRIGGER my_table_history_tr BEFORE UPDATE OR INSERT
    ON my_table FOR EACH ROW
    EXECUTE PROCEDURE my_table_history_fn();

I tried it out and it works in version 8.1.

==================================================================
   Aaron Bono
   Aranya Software Technologies, Inc.
   http://www.aranya.com
==================================================================

Re: Triggers using PL/pgSQL

От:

"Aaron Bono" <postgresql@aranya.com>

Дата:

31 июля 2006 г. в 02:35:16

No problem. I have been meaning to put the same code together for myself but have been putting it off. It gave me an excuse to stop procrastinating.

On 7/31/06, Thusitha Kodikara <kthusi@yahoo.com> wrote:

Hello,

Thanks a lot Aaron for the very quick and simple example. I just checked it on 7.4.5 also and it worked. I'll be able to continue with my development using the syntax of that example.

Aaron Bono < postgresql@aranya.com> wrote:
On 7/30/06, Thusitha Kodikara < kthusi@yahoo.com> wrote:
Hello,

I am interested in developing some triggers to keep track of records that are changed (possibly the changes of one or more specific columns). In addition to keeping the new values, I also need to keep the old values (may be on a separate table).

Though I have done similar things in other RDBMs using SQL, I find doing this in Postgres, a little bit complicated - may be because it needs to be done through a separate procedural language and through a separate function. The Postgres documentation also didn't provide much help ( the examples in C). I was mainly looking for example showing how to refer 'OLD' and 'NEW' rows using PL/pgSQL.

Can someone please direct me to some such examples?

How about this:

CREATE TABLE my_table (
   my_table_id BIGSERIAL NOT NULL,
   my_value VARCHAR(100) NOT NULL,
   CONSTRAINT my_table_pkey PRIMARY KEY (my_table_id)
);

CREATE TABLE my_table_history (
   my_table_id BIGINT NOT NULL,
   my_value VARCHAR(100) NOT NULL,
   create_dt TIMESTAMP NOT NULL,
   CONSTRAINT my_table_history_pkey PRIMARY KEY (my_table_id, create_dt)
);

CREATE OR REPLACE FUNCTION my_table_history_fn () RETURNS SETOF opaque AS
'
BEGIN
    -- if a trigger insert or update operation occurs
    IF TG_OP = ''INSERT'' OR TG_OP = ''UPDATE'' THEN
        execute
            ''INSERT INTO my_table_history ( '' ||
            ''    my_table_id, '' ||
            ''    my_value, '' ||
            ''    create_dt '' ||
            '') VALUES ( '' ||
            ''    '''''' || NEW.my_table_id || '''''', '' ||
            ''    '''''' || NEW.my_value || '''''', '' ||
            ''    now() '' ||
            '');''
        ;
        RETURN NEW;
    END IF;
END;
'
LANGUAGE 'plpgsql' VOLATILE CALLED ON NULL INPUT SECURITY INVOKER;

CREATE TRIGGER my_table_history_tr BEFORE UPDATE OR INSERT
    ON my_table FOR EACH ROW
    EXECUTE PROCEDURE my_table_history_fn();

I tried it out and it works in version 8.1.

Re: Triggers using PL/pgSQL

От:

"Aaron Bono" <postgresql@aranya.com>

Дата:

31 июля 2006 г. в 12:00:41

On 7/31/06, John DeSoi <desoi@pgedit.com> wrote:

Is it really necessary to build a SQL string and use execute? It
seems you could just issue the INSERT statement.

I don't think so but there was some discussion a week or two ago about mixing variables and using execute. I am curious, does anyone know what the "best" approach is?

Also, I did not address deletions. If you still need to delete from the table, you will need to get rid of the foreign key on the history table. You will also need to decide how the history table will reflect the recording of those deletions.

I usually don't allow deletes on tables (unless absolutely necessary) and instead add start/end dates to the tables so rows can be marked as removed. Then I add a view that filters out the inactive rows - all applications use the views, they do not query the tables directly. This also allows you to "delete" rows at sometime in the future or make them appear in the future too.

On Jul 31, 2006, at 12:52 AM, Aaron Bono wrote:

> CREATE OR REPLACE FUNCTION my_table_history_fn () RETURNS SETOF
> opaque AS
> '
> BEGIN
>     -- if a trigger insert or update operation occurs
>     IF TG_OP = ''INSERT'' OR TG_OP = ''UPDATE'' THEN
>         execute
>             ''INSERT INTO my_table_history ( '' ||
>             ''    my_table_id, '' ||
>             ''    my_value, '' ||
>             ''    create_dt '' ||
>             '') VALUES ( '' ||
>             ''    '''''' || NEW.my_table_id || '''''', '' ||
>             ''    '''''' || NEW.my_value || '''''', '' ||
>             ''    now() '' ||
>             '');''
>         ;
>         RETURN NEW;
>     END IF;
> END;
> '
> LANGUAGE 'plpgsql' VOLATILE CALLED ON NULL INPUT SECURITY INVOKER;

John DeSoi, Ph.D.
http://pgedit.com/
Power Tools for PostgreSQL

--
==================================================================
   Aaron Bono
   Aranya Software Technologies, Inc.
   http://www.aranya.com
==================================================================

Re: Triggers using PL/pgSQL

От:

John DeSoi <desoi@pgedit.com>

Дата:

31 июля 2006 г. в 12:55:46

Re: [PHP] PL/pgSQL and PHP 5

От:

John DeSoi <desoi@pgedit.com>

Дата:

10 августа 2006 г. в 03:13:04

Glad you found the problem.

On Aug 9, 2006, at 11:42 PM, PostgreSQL Admin wrote:

> $connection->execute("SELECT insert_staff_b('$staff 
> [insert_firstname]'::varchar)");
> $connection->execute("SELECT insert_staff_b('".$staff 
> ['insert_firstname']."'::varchar)");

If you are creating SQL functions you want to call from PHP, you  
might be interested in this simple class:

http://pgedit.com/resource/php/pgfuncall

Then instead of all the quoting issue you have above, you could  
simply call your SQL function like a normal PHP method call:

$connection->insert_staff_b($staff['insert_firstname']);

John DeSoi, Ph.D.
http://pgedit.com/
Power Tools for PostgreSQL