CODEWITHSUNDEEP
functionpostgresqlsql-injectionplpgsqldynamic-sql
user430926
user430926

Reputation: 4057

Select from dynamic table in function without using EXECUTE

Can we select inside a PostgreSQL function without using EXECUTE? I'm trying to use quote_ident() to create dynamic SQL but it doesn't work.

CREATE OR REPLACE FUNCTION select_server(p_id text)
RETURNS integer AS $$ 
DECLARE 
   serialnum_value INTEGER; 
   STATEMENT TEXT;
BEGIN
  STATEMENT := 'tbl' || substr($1, 1, 4);  
  SELECT serialnum INTO serialnum_value FROM quote_ident(STATEMENT ) WHERE id = $1;
  RETURN serialnum;
END;

Does anybody have idea how to select from dynamic table in a PostgreSQL function without using EXECUTE?

Upvotes: 0

Views: 729

Answers (2)

Erwin Brandstetter
Erwin Brandstetter

Reputation: 656153

You cannot execute dynamic SQL without EXECUTE in PL/pgSQL. That's what makes it dynamic in the first place.

But you can streamline your function quite a bit:

CREATE OR REPLACE FUNCTION select_server(p_id text, OUT serial_value integer)
 LANGUAGE plpgsql STRICT AS
$func$ 
BEGIN
   EXECUTE 'SELECT serialnum FROM tbl' || left($1::text, 4)) || ' WHERE id = $1'
   USING $1
   INTO  serial_value;
END
$func$;

  • Normally you would have to sanitize any identifier.
    But you do not need quote_ident() in this special case, since the only dynamic component are 4 digits from an integer. Neither SQL injection nor illegal identifiers are possible.

  • Pass the value of p_id as value using the USING clause.

  • Reduce the number of assignments. Those are comparatively expensive in PL/pgSQL. You only need a single SQL statement to do everything.

  • The OUT parameter helps to shorten the syntax.

  • I also made the function STRICT (RETURNS NULL ON NULL INPUT), since it would not make sense with NULL as input.

  • left() is slightly faster than substr().

Upvotes: 1

Denis de Bernardy
Denis de Bernardy

Reputation: 78413

You can't avoid execute here, per igor's comment. The best alternative is to create a function that creates a function, which you can then call as needed. Example:

create function gen_select_server(p_id text)
  returns boolean
as $gen$
begin
  execute $exec$
  create function $exec$ || quote_ident('select_server_' || p_id) || $exec$
    returns integer
  as $body$
  begin
    return serialnum
    from $exec$ || quote_ident('tbl_' || p_id) || $exec$
    where id = $exec$ || quote_literal('id_' || p_id) || $exec$;
  end;
  $body$ language plpgsql;
  $exec$;
  return true;
end;
$gen$ language plpgsql;

-- usage:
select gen_select_server('1234');   -- call this only once; uses execute
select * from select_server_1234(); -- no execute here

As the above highlights, it can get messy with string delimiters. Also note that the above isn't your original function -- it's primarily to illustrate how to quote things properly within the big $exec$ "string" block.

I'd recommend to stick to using execute, that being said. Or using an ORM, for that matter. Maintaining these micro-optimizations is potentially a pain worse than the superficial gain in performance.

Upvotes: 1

Related Questions