Refactor a PL/pgSQL function to return the output of various SELECT queries

user_1530410 0
Viewed 34616

I wrote a function that outputs a PostgreSQL SELECT query well formed in text form. Now I don't want to output a text anymore, but actually run the generated SELECT statement against the database and return the result - just like the query itself would.

What I have so far:

CREATE OR REPLACE FUNCTION data_of(integer)
  RETURNS text AS
$BODY$
DECLARE
   sensors varchar(100);   -- holds list of column names
   type    varchar(100);   -- holds name of table
   result  text;           -- holds SQL query
       -- declare more variables

BEGIN
      -- do some crazy stuff

      result := 'SELECT\r\nDatahora,' || sensors ||
      '\r\n\r\nFROM\r\n' || type ||
      '\r\n\r\nWHERE\r\id=' || $1 ||'\r\n\r\nORDER BY Datahora;';

      RETURN result;
END;
$BODY$
LANGUAGE 'plpgsql' VOLATILE;
ALTER FUNCTION data_of(integer) OWNER TO postgres;

sensors holds the list of column names for the table type. Those are declared and filled in the course of the function. Eventually, they hold values like:

  • sensors: 'column1, column2, column3'
    Except for Datahora (timestamp) all columns are of type double precision.

  • type :'myTable'
    Can be the name of one of four tables. Each has different columns, except for the common column Datahora.

Definition of the underlying tables.

The variable sensors will hold all columns displayed here for the corresponding table in type. For example: If type is pcdmet then sensors will be 'datahora,dirvento,precipitacao,pressaoatm,radsolacum,tempar,umidrel,velvento'

The variables are used to build a SELECT statement that is stored in result. Like:

SELECT Datahora, column1, column2, column3
FROM   myTable
WHERE  id=20
ORDER  BY Datahora;

Right now, my function returns this statement as text. I copy-paste and execute it in pgAdmin or via psql. I want to automate this, run the query automatically and return the result. How can I do that?

dynamic-sql plpgsql postgresql sql database
4 Answers
# copy paste me into bash shell directly
clear; IFS='' read -r -d '' sql_code << 'EOF_SQL_CODE'
CREATE OR REPLACE FUNCTION func_get_all_users_roles()
  -- define the return type of the result set as table
  -- those datatypes must match the ones in the src
  RETURNS TABLE (
                 id           bigint
               , email        varchar(200)
               , password     varchar(200)
               , roles        varchar(100)) AS
$func$
BEGIN
   RETURN QUERY 
   -- start the select clause
   SELECT users.id, users.email, users.password, roles.name as roles
   FROM user_roles
   LEFT JOIN roles ON (roles.guid = user_roles.roles_guid)
   LEFT JOIN users ON (users.guid = user_roles.users_guid)
   -- stop the select clause
;
END
$func$  LANGUAGE plpgsql;
EOF_SQL_CODE
# create the function
psql -d db_name -c "$sql_code"; 

# call the function 
psql -d db_name -c "select * from func_get_all_users_roles() "
YordanGeorgiev_657060
Related