CODEWITHSUNDEEP
postgresqlexecute
SNpn
SNpn

Reputation: 2207

Postgresql Execute function

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.

Upvotes: 0

Views: 1811

Answers (1)

user330315
user330315

Reputation:

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

Upvotes: 1

Related Questions