CODEWITHSUNDEEP
postgresql
Aaron
Aaron

Reputation: 2755

Function: Use text/varchar parameter as a query

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');

Upvotes: 0

Views: 51

Answers (1)

Kaushik Nayak
Kaushik Nayak

Reputation: 31648

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');

Upvotes: 1

Related Questions