Function: Use text/varchar parameter as a query

Question

I have a query that I use in different parts of a system. Now I want to make a function wherein it accepts a text to be used as a query, I'm not even sure if this is possible. However, I want to know if there's a possible workaround for this.

What I want to achieve is a function that returns a table and accepts a text/varchar that could be used as a query.

Here's what I have, I have a query that is kind of a "base query" that can have different data based on the given CTE which is named:

data_table

refer to the function and its usage below - this is mostly abstract but imo this should be enough.

CREATE OR REPLACE FUNCTION func(
  data_query TEXT
)
  RETURNS TABLE
          (
            id             BIGINT,
            random_text    varchar
          )
  LANGUAGE plpgsql
AS $function$
BEGIN
  RETURN QUERY
  with data_table AS (
    data_query
  ), another_data_table AS (
    SELECT
      *
    FROM my_data
  )
  SELECT
    d.id,
    ad.random_text
  FROM data_table d
  INNER JOIN another_data_table ad
    ON ad.ref_id = d.id;
END; $function$;

usage:

SELECT * FROM func('SELECT * FROM my_data_table');
SELECT * FROM func('SELECT * FROM my_second_data_table');

You need to use dynamic SQL. Mind that you cannot avoid an SQL injection risk with such a setup, so only use it for trusted queries. — Laurenz Albe
– Laurenz Albe, Commented May 21, 2019 at 5:24

Kaushik Nayak · Accepted Answer · 2019-05-21 05:31:42Z

1

Instead of passing the query, you may pass the table name and access it dyamically in your query using EXECUTE format

CREATE OR REPLACE FUNCTION func(
  table_name_in TEXT
)
  RETURNS TABLE
          (
            id             BIGINT,
            random_text    varchar
          )
  LANGUAGE plpgsql
AS $function$
BEGIN
  RETURN QUERY EXECUTE format (
  'SELECT
    d.id :: bigint,
    ad.random_text :: varchar
     FROM %I d
  INNER JOIN my_data ad
    ON ad.ref_id = d.id', table_name_in );
END
$function$;

usage:

SELECT * FROM func('my_data_table');
SELECT * FROM func('my_second_data_table');

answered May 21, 2019 at 5:31

Kaushik Nayak

32k6 gold badges36 silver badges54 bronze badges

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

Collectives™ on Stack Overflow

Function: Use text/varchar parameter as a query

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