Postgresql nest SELECT statement within a function call

Question

i currently setting up full text search in Postgresql database as described in this manual page: http://www.postgresql.org/docs/9.0/static/textsearch-tables.html

Currently i came out with this statement:

CREATE FUNCTION fts_update() RETURNS trigger AS $$
begin
  new.tsv :=
       to_tsvector('pg_catalog.english', coalesce(new.title,'')) ||
       to_tsvector('pg_catalog.english', coalesce(new.description,'')) ||
       to_tsvector('pg_catalog.english',
        SELECT array_to_string(array_accum(name), ' ') FROM agencies where id = new.agency_id
       );
  return new;
end
$$ LANGUAGE plpgsql;
CREATE TRIGGER ftsupdate_trigger BEFORE INSERT OR UPDATE ON camps FOR EACH ROW EXECUTE PROCEDURE fts_update()"

Here i need to add some fields from another table to index, but Postgresql not allowing me:

PG::Error: ERROR:  syntax error at or near "SELECT"
LINE 9:         SELECT array_to_string(array_accum(name), ' ') FROM ...

Is there any good fix for this ?

Gavin · Accepted Answer · 2012-11-29 09:56:56Z

1

I think all you need to do is wrap the select in brackets e.g.

 select to_tsvector('english',(select array_to_string(array['one','two','three'],' ')));

answered Nov 29, 2012 at 9:56

Gavin

6,5603 gold badges28 silver badges25 bronze badges

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

Collectives™ on Stack Overflow

Postgresql nest SELECT statement within a function call

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related