Home >Database >Mysql Tutorial >How Can I Refactor a PL/pgSQL Function to Return Results from Dynamic SELECT Queries with Variable Column Numbers and Types?
You have a PL/pgSQL function that generates a SELECT query as a text string. Now you want to further enhance this function to execute the generated query and return actual results, similar to the behavior of a standalone query.
The challenge is that the structure and type of data returned may vary depending on the underlying table being queried. A simple solution is to define a fixed return type with a common column name and cast all values to text, as in the modified function like this:
<code class="language-sql">CREATE OR REPLACE FUNCTION data_of(_id integer) RETURNS TABLE (datahora timestamp, col2 text, col3 text) LANGUAGE plpgsql AS $func$ DECLARE _sensors text := 'col1::text, col2::text'; _type text := 'foo'; BEGIN RETURN QUERY EXECUTE ' SELECT datahora, ' || _sensors || ' FROM ' || quote_ident(_type) || ' WHERE id = ORDER BY datahora' USING _id; END $func$;</code>
If the number of columns returned changes, but the types remain the same (e.g. all are float8), you can use an array of nested values:
<code class="language-sql">CREATE OR REPLACE FUNCTION data_of(_id integer) RETURNS TABLE (datahora timestamp, names text[], values float8[]) LANGUAGE plpgsql AS $func$ DECLARE _sensors text := 'col1, col2, col3'; _type text := 'foo'; BEGIN RETURN QUERY EXECUTE format(' SELECT datahora , string_to_array() -- AS names , ARRAY[%s] -- AS values FROM %s WHERE id = ORDER BY datahora' , _sensors, _type) USING _sensors, _id; END $func$;</code>
To handle various complete table types with different structures, you can take advantage of polymorphic types:
<code class="language-sql">CREATE OR REPLACE FUNCTION data_of(_tbl_type anyelement, _id int) RETURNS SETOF anyelement LANGUAGE plpgsql AS $func$ BEGIN RETURN QUERY EXECUTE format(' SELECT * FROM %s -- pg_typeof returns regtype, quoted automatically WHERE id = ORDER BY datahora' , pg_typeof(_tbl_type)) USING _id; END $func$;</code>
This function requires that you pass a NULL value that is cast to the target table type (for example, NULL::pcdmet). It then automatically determines the appropriate row type and returns the individual columns when used with SELECT * FROM data_of().
The above is the detailed content of How Can I Refactor a PL/pgSQL Function to Return Results from Dynamic SELECT Queries with Variable Column Numbers and Types?. For more information, please follow other related articles on the PHP Chinese website!