PL/PostgreSQL how to convert a variable into a table name

Question

So I have a function in PostgreSQL that dynamically selects columns from a dynamic table. I got this solution from this post and it works great other than one thing.

This is inside of a file that is connected to a Node server, and so the $1 and $2 in the second SELECT * FROM represent values passed from there. The issue right now is that I am getting a syntax error that I don't understand (I am newer to SQL so that may be why).

$2 represents the name of the table to be selected from as a string, so for example it could be 'goals'. The error is syntax error at or near "'goals'". I realize that it cannot be a string with single quotes (I believe) and so I am wondering how to convert that variable to be a table name? using "goals" there as well as goals, for example works as expected, though I'm not sure how to do that outside of a function.

CREATE OR REPLACE FUNCTION get_data(user_id INT, table_name anyelement)
RETURNS SETOF ANYELEMENT AS $$
    BEGIN
        RETURN QUERY EXECUTE 
            format('SELECT * FROM %s WHERE user_id = $1', pg_typeof(table_name)) USING user_id;
    END;
$$ LANGUAGE plpgsql;

SELECT * FROM get_data($1, NULL::$2);

$1 is 5 and $2 is 'goals' for example

This is broken in many ways. 1) Use `table_name varchar`. 2) Read the section in docs, [Format](https://www.postgresql.org/docs/current/functions-string.html#FUNCTIONS-STRING-FORMAT) for what are correct parameters. 3) Read this section, [Dynamic SQL](https://www.postgresql.org/docs/current/functions-string.html#FUNCTIONS-STRING-FORMAT) on how to build and `EXECUTE` dynamic SQL. 4) This docs section [System Info functions](https://www.postgresql.org/docs/current/functions-string.html#FUNCTIONS-STRING-FORMAT) for what `pg_typeof` does. — Adrian Klaver, Dec 31 '20 at 03:10
Hint, think `format('SELECT * FROM %I WHERE user_id = $1', table_name) USING user_id;` — Adrian Klaver, Dec 31 '20 at 03:12
@AdrianKlaver the reason I used anyelement instead of varchar is because without anyelement it throws an error. Initially I was using `RETURNS SETOF RECORD`, however with record I would need to create a datatype of each of my columns, thus removing the dynamic nature of it. I updated my format with the %I. Without the NULL I get the same error with needing to specify a datatype for each of the columns, and with it I get the syntax error. In the Dynamic SQL I couldn't seem to find anything that I could use for this particular example, though I may have missed it! Thanks for the responses! — Jabinator1, Dec 31 '20 at 06:28
Yeah, this won't work. Not sure what the purpose is anyway. Why not just do the `SELECT` outside a function? — Adrian Klaver, Dec 31 '20 at 17:45
@AdrianKlaver I'm trying to make a function that dynamically selects a table based on the name given, otherwise I'd have to make tons of sql files for each of the SELECT * FROM tablename which I was hoping to avoid — Jabinator1, Dec 31 '20 at 19:49
No you don't. You just make a template `SQL` string and substitute the table name into the template. — Adrian Klaver, Dec 31 '20 at 19:54
as in `SELECT * FROM ${tableName} WHERE user_id = ${userId}` ? (with a backtick around it) — Jabinator1, Dec 31 '20 at 20:02

score 0 · Accepted Answer · answered Dec 31 '20 at 21:17

After many hours of trying to figure it out, thanks to Adrian's comment, I found MassiveJS (how I'm connecting to my PostgreSQL server) has inline functions to do queries. In my controller file in my server I was able to create a one line function as such:

const data = await db[tableName].where("user_id=$1", [userId])

Didn't know inline SQL existed in MassiveJS, so that was great to find out!

PL/PostgreSQL how to convert a variable into a table name

1 Answers1

Linked