Pass a database query parameter as a function casted parameter

Wappler General Need Help
,
1

So I know you can pass query parameters as function parameter:

This:

CREATE FUNCTION add_one (integer) RETURNS INTEGER AS '
    BEGIN
        RETURN $1 + 1;
    END;
' LANGUAGE 'plpgsql';

Will return correctly the value.

image
image700Ă—297 8.65 KB

However I have a problem when the parameter can expect any type of data. I need to cast the parameter with NULL::

So the parsing will fail for this:

SELECT * FROM public.get_table(NULL::schema.tablename)

SELECT * FROM public.get_table(NULL:::P1)

Is there any way that the parameter can be set to not start with “:” or a way to add it in the function parameter but parsed correctly?

3

SELECT * FROM public.get_table(NULL::?)

This will not work either.

4

@patrick

As a matter of fact I don’t think it has anything to do with the choice of delimiter. But the fact that I can’t manage to cast the parameter itself.

Any ideas?

5

As an alternative I could always write a Custom Module:
Something in these terms:

let module_param = 'public.user' //Comes from module options
let table = 'NULL::'+module_param        
let results = await db.raw('SELECT * FROM public.get_table('+table+')');

But before I delve into that it would be great to have confirmation that I won’t be able to cast the parameter from a standard custom query.

6

Anyway I finally went via custom module but with knex. This way I can avoid having to use a PL/pgsql function.

const db = require('../../../lib/core/db');

exports.anytable = async function(options) {
    // connection option is required
    const connection = this.parseRequired('db', 'string', 'connection is required.');
    const table = this.parseRequired(options.table, 'string', 'table is required.');
    // get the database connection
    const db = this.getDbConnection(connection);
    // throw error if connection not found
    if (!db) throw new Error(`Connection "${connection}" doesn't exist.`);
    // get results from database
    return db.select('*').from(table);
  }
1 Like