Getting row values based on array of column names passed to PostgreSQL function

Question

I'm trying to set up full text search in PostgreSQL 9.2. I created a new table to hold the content that I want to search (so that I can search across lots of different types of items), which looks like this:

CREATE TABLE search (
    target_id bigint PRIMARY KEY,
    target_type text,
    fts tsvector
);

CREATE INDEX search_fts ON search USING gin(fts);

Every time a new item gets inserted (or updated) into one of the various tables I want to search across, it should automatically be added to the search table. Assuming that my table looks like the following:

CREATE TABLE item (id bigint PRIMARY KEY, name text NOT NULL, description text);

I created a trigger passing in the column names that I want to be able to search:

CREATE TRIGGER insert_item_search BEFORE INSERT
    ON item FOR EACH ROW EXECUTE PROCEDURE
    insert_search('{name, description}'::text[]);

Then created a new function insert_search as:

CREATE OR REPLACE FUNCTION insert_search(cols text[]) RETURNS TRIGGER AS $$
BEGIN
    INSERT INTO search (target_id, target_type, fts) VALUES (
      NEW.id, TG_TABLE_NAME, to_tsvector('english', 'foo')
    );
    RETURN NEW;
END;
$$ LANGUAGE PLPGSQL;

My question is, how do I pass in the table values based on cols to to_tsvector? Right now, the function is getting called and inserts the id and type correctly, but I don't know the right way to dynamically grab the other values based on the cols argument.

MatheusOl · Accepted Answer · 2013-02-22 19:08:49Z

5

First, to pass arguments, just send them directly:

CREATE TRIGGER insert_item_search BEFORE INSERT
    ON item FOR EACH ROW EXECUTE PROCEDURE
    insert_search('name', 'description');

And, from PL/pgSQL you will get those arguments as an array, called TG_ARGV. But, the problem is that PL/pgSQL cannot get the values from NEW record based on their names. To do that you can either use a language that lets you do that (like PL/python or PL/perl) or use the hstore extension.

I'd stick with the last one and use hstore (unless you already use one of the other languages to create functions):

CREATE OR REPLACE FUNCTION insert_search() RETURNS TRIGGER AS $$
DECLARE
        v_new hstore;
BEGIN
        v_new = hstore(NEW); -- convert the record to hstore
        FOR i IN 0..(TG_NARGS-1) LOOP
                INSERT INTO search (target_id, target_type, fts) VALUES (
                  NEW.id, TG_TABLE_NAME, to_tsvector('english', v_new -> TG_ARGV[i])
                );
        END LOOP;
    RETURN NEW;
END;
$$ LANGUAGE PLPGSQL;

As you can see above, I used the hstore's operator -> to get the value based on the name (on TG_ARGV[i]).

answered Feb 22, 2013 at 19:08

MatheusOl

12k3 gold badges33 silver badges29 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Bill Over a year ago

Thanks! I ended up creating the tsvector first and then inserting into the search table (since INSERT in the loop breaks primary key constraint) but this got me most of the way there.

MatheusOl Over a year ago

@Bill, as you were passing two arguments, I thought you needed to insert multiple registers... But you can adapt as you wish, I'm glad it worked...

Scott S · Accepted Answer · 2013-02-22 18:44:41Z

0

You can access the parameters specified in the trigger definition with the TG_ARGV variable. You can find documentation on that here. TG_ARGV is an array accessed by a 0 based index. So it would be something like TG_ARGV[0], TG_ARGV[1], and so on.

answered Feb 22, 2013 at 18:44

Scott S

2,73618 silver badges21 bronze badges

Collectives™ on Stack Overflow

Getting row values based on array of column names passed to PostgreSQL function

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related