Thread: search_path in SQL script?

<fontfamily><param>Lucida Grande</param>I'm working on a simple SQL
script, and I'd like a method of appending my newly created schema to
the existing search_path.


any help would be appreciated

Jeffrey Melloy

jmelloy@visualdistortion.org

</fontfamily>
I'm working on a simple SQL script, and I'd like a method of appending
my newly created schema to the existing search_path.

any help would be appreciated
Jeffrey Melloy
jmelloy@visualdistortion.org

Jeffrey Melloy <jmelloy@visualdistortion.org> writes:
> I'm working on a simple SQL script, and I'd like a method of appending
> my newly created schema to the existing search_path.

You could read current_schemas() and build a new value to set
search_path to.  But I fear that a plain noninteractive script file
may not have an easy way to do that ...

            regards, tom lane

Hi.  I'm fruitlessly banging my head against a trigger.  Which is
causing an 'update' to fail.  Which is not my aim.  There is nothing
obvious wrong, but maybe a member of this group can see something I cannot.

It does not return any error, and replies on the command line with the
usual:

=# UPDATE 1

Yet no update has taken place.  Something about my trigger is messing it up.

My aim is to update my relation 'orders' with a summary of it's child
relation 'item'.

Brefly (and in lower case, sorry):

create table orders (
    code serial8 not null primary key,
    sum_items smallint not null default 0,    -- Summary field
) ;

create table item (
    orders integer not null,
    descript varchar(50) not null default '',
    primary key (orders, descript),
    foreign key (orders) references orders (code) on delete cascade,
    items smallint not null default 0,    -- Source field
) ;


CREATE FUNCTION t_dec_item_summary ()
RETURNS trigger
AS '
   BEGIN
     update orders set
       item_count = item_count - 1
     WHERE code = OLD.orders;
     RETURN OLD;
   END;
' language 'plpgsql';


create trigger item_00_change
   before delete or update
   on item for each row
   execute procedure t_dec_item_summary ();


Example:

=# select orders, descript, items from item ;
1 1 1

=# update item set items = 2 where orders = 1 and descript = '1' ;
UPDATE 1

=# select orders, descript, items from item ;
1 1 1

Therefore, no difference.  The command appears to have failed.

Drop the trigger:

=# drop trigger item_00_change ;

=# update item set items = 2 where orders = 1 and descript = '1' ;
UPDATE 1

=# select orders, descript, items from item ;
1 1 2

And it will work.  It will also completelly works on 'delete', which
calls the same trigger.

I do know with certainty that the trigger has fired.


What is there about my trigger is causing the command to fail without
reporting an error?


Something about my function t_dec_item_summary is causing the UPDATE to
fail to update the values, or update with the same values as already
existed.


Am I returning the correct thing?  Should I force a return of 'TRUE' or
'FALSE' or something?


Ever thankful of amazing help from this group,

Ben