replace table name in query with GET parameter

[ersi33]

Hi,
I would like to test the new 'json' component with PostgreSQL, and with a table name in the url (GET parameter).
When i try it with a table named 'article', the query below work well:
select 'json' as component,
(json_build_object(article, (select json_agg(x) from (select * from article order by 1 limit 2) as x))) as contents;

But if i want to add a 'table' parameter in the URL (ie example: 'jsontest.sql?table=article') and to replace in the query the table name with $table:
select 'json' as component,
(json_build_object($table, (select json_agg(x) from (select * from $table order by 1 limit 2) as x))) as contents;

It seem that the first occurence of $table is well replaced, but not the second one and it give me:

Error:
SQL syntax error before: * from $table order by 1 limit 2 ) as x ) ) ) as contents
Caused by:
0: sql parser error: Expected ), found: *
1: An error occurred during the preparation phase of the SQL

I suspect again some security reasons, but maybe i forgot something ...

PS: And many thanks for this super genial open source project.

[lovasoa]

Indeed, postgres does not support variable table names in prepared statements (nor do any other database). It's mostly a good thing, both for security reasons, as you mentioned (most people don't want to give open access to all tables, including internal ones, to everyone) and for performance reasons (Postgres needs to know which table it will be querying to make a good query plan).

If you don't care about any of that and just want to give everyone access to everything, you can create a postgres function with an EXECUTE statement.

In your migrations:

CREATE FUNCTION select_all_from(tablename text, OUT table_as_array jsonb)
AS '
BEGIN
	EXECUTE format(''SELECT json_agg(row_to_json(%I)) FROM %I '', tablename, tablename) INTO table_as_array;
END
' LANGUAGE plpgsql;

in your api.sql :

SELECT
	'json'                   AS component,
 	select_all_from($table)  AS contents;

Live example: https://www.db-fiddle.com/f/wrTmdVULH7GC1JAUi4ij7X/1

That said, what I would do is just manually select which tables I want to create an API endpoint for, and create a small sql file for each one.

[ersi33]

Thanks for your response, it's very clear.