Search code examples
sqlpostgresqldynamic-sqlsql-function

I need help regarding dynamic SQL and dynamically setting the table name in a function

So, i need to create a function that returns the count of the lines in a table, the table name must be specified by the user. This is the code i have:

CREATE OR REPLACE FUNCTION datiInTab( table regclass )
RETURNS NUMERIC(5) AS
$$
DECLARE
num NUMERIC(5);
cmd VARCHAR(1000):='SELECT COUNT(*) FROM ';
BEGIN
EXECUTE cmd || table;
RETURN num;
END;
$$
LANGUAGE plpgsql;
SELECT * FROM datiInTab(corsi);

And this is the error that returns after i execute:

ERROR: ERRORE: la colonna "corsi" non esiste 
LINE 1: SELECT * FROM datiInTab(corsi); ^ SQL state: 42703 Character: 25

Meaning that the column "corsi" doesn't exist. I debugged as much as i could and the only conclusion is that i don't really know what i need to do in order to make use of this dynamic call.

edit: regclass was a last minute addition, after trying with a simple varchar, that returned the same exact error

Solution

  • You can't name a variable table. it is a reserved word

    and you have to pass a string else postgres thinks you want to pass a column

    CREATE OR REPLACE FUNCTION datiInTab( table1 regclass )
RETURNS NUMERIC(5) AS
$$
DECLARE
num NUMERIC(5);
cmd VARCHAR(1000):='SELECT COUNT(*) FROM ';
BEGIN
EXECUTE cmd || table1;
RETURN num;
END;
$$
LANGUAGE plpgsql;

    ✓

    SELECT * FROM datiInTab('corsi'); 

    ERROR:  relation "corsi" does not exist

    LINE 1: SELECT * FROM datiInTab('corsi'); ^

    db<>fiddle here