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

I'm trying to create a function that searches for a string in one of the table fields and then returns a new table of users. Basically, inside one table I have

create table fooSearchTable (
    id          integer, -- PG: serial
    name        LongName unique not null,
    queryDef    LongString not null,
    primary key (id)
);

where queryDef is a string that holds another query to execute. For example one row might be 

1 | resultName | select * from users where users.id = '4'

I was given this function format to start off the function with

create or replace function searchUsers(_searchName text) returns table (userID integer) as $$
begin
end;
$$ language plpgsql stable;

I need to run a SQL query to find the row the _searchName matches with that is

select queryDef from fooSearchTable f
where f.name = _searchName;

this would return the string, but I don't know how to execute this string in the function so i can get a table of userIDs. Any help would be appreciated.

share|improve this question
    
I gather you've read what the fine manual says about this topic? –  Milen A. Radev May 23 '12 at 7:09
    
@MilenA.Radev yup a little confused how to apply it from the one shown in the manual –  SNpn May 23 '12 at 7:19

1 Answer 1

up vote 1 down vote accepted

Something like this should work:

create or replace function searchUsers(_searchName text) 
   returns table (userID integer) 
as $$
  _query varchar;
begin
  select queryDef
     into _query 
  from fooSearchTable f
  where f.name = _searchName;

  return query execute _query;
end
$$ language plpgsql;

(Not tested, so it might contain syntax erros)

Note that select .. into requires the statment to return exactly one row, otherwise you'll get an error at runtime. You either need to make sure the condition achieves this or apply a limit clause on the select statement.

This is explained here:
http://www.postgresql.org/docs/current/static/plpgsql-control-structures.html#AEN54092

share|improve this answer

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.