How to translate a Select with uuid to plpgsql in java

Question

I have the following simple select:

SELECT * FROM db.table WHERE uuid::varchar LIKE ('389519a6-ba79-4a4d-a696-c818808d3b26');

which works just fine in pgadmin v4.

Now I try to do the same with a plpgsql function:

 queries[0] = "DROP FUNCTION createtemptable(text,text,text);"
            + "CREATE OR REPLACE FUNCTION createTempTable(name TEXT, id TEXT, units TEXT) "
            + "RETURNS void AS $$ "
            + "DECLARE "
            + "nameid TEXT; "
            + "BEGIN "
            + "nameid := name || id;"
            + "EXECUTE 'SET search_path = schema_name; "
            + "CREATE TABLE IF NOT EXISTS ' || quote_ident(nameid) || ' "
            + "AS SELECT * FROM ' || quote_ident(name) || ' "
            + "WHERE uuid LIKE ' || uuid(units) ;"
            + "END; $$ "
            + "LANGUAGE plpgsql;";

resulting in:

DROP FUNCTION createtemptable(text,text,text);
CREATE OR REPLACE FUNCTION createTempTable(name TEXT, id TEXT, units TEXT)
RETURNS void AS $$ 
DECLARE nameid TEXT; 
BEGIN 
    nameid := name || id;
    EXECUTE 
        'SET search_path = opsim; 
        CREATE TABLE IF NOT EXISTS ' || quote_ident(nameid) || ' AS 
            SELECT * FROM ' || quote_ident(name) || ' 
                WHERE uuid LIKE ' || uuid(units);
END; $$ LANGUAGE plpgsql;

In the end I want to hand over an array of unit uuids and have a temp_table created for them. In the last few hours I traced the error back to the select on top.

What's weird: it works just fine in pgadmin, but fails with:

org.postgresql.util.PSQLException: Error: Syntaxerror at „a6“

It doesn't matter if I keep both as uuids, or compare them as strings, it always breaks at that point. And I can't find out why.

According to auntie google and everything I researched in the last 4 hours, it should work. Anyone an idea where I forgot to mask something or ... whatever?

EDIT: improved on suggestions from comments. I can't quote_ident units, as this would add unwanted quotes.

You're not properly quoting any of your inputs, which (aside from being a risky injection point) is resulting in a broken query. Check quote_literal() and quote_ident() for quoting your inputs before embedding them in that query string or use format() to quote and embed in one shot. — yieldsfalsehood

a_horse_with_no_name a_horse_with_no_name · Accepted Answer · 2016-10-18T06:22:24

Use format() to safely format dynamic SQL statements:

CREATE OR REPLACE FUNCTION createTempTable(name TEXT, id TEXT, units TEXT) 
   RETURNS void 
AS 
$$ 
BEGIN 
  EXECUTE format(
    'CREATE TABLE IF NOT EXISTS db.%I AS 
     SELECT * 
     FROM db.%I
     WHERE uuid = %L', name||id, name, units);
END; 
$$ LANGUAGE plpgsql;

It's unclear to me why you are using where uuid IN (...) if you are only passing a single value.

If you want to pass multiple values for the UUID, use an array rather then a comma separated string:

CREATE OR REPLACE FUNCTION createTempTable(name TEXT, id TEXT, units TEXT[]) 
   RETURNS void 
AS 
$$ 
BEGIN 
  EXECUTE format(
    'CREATE TABLE IF NOT EXISTS %I AS 
     SELECT * 
     FROM %I
     WHERE uuid::varchar = any($1)', name||id, name) using units;
END; 
$$ LANGUAGE plpgsql;

You use it like this:

select createtemptable(
        'anders', 
        'one', 
        array['389519a6-ba79-4a4d-a696-c818808d3b26','389519a6-ba77-4a4d-a696-c818808d3b26']
       );

Here is an example: http://rextester.com/SOAK47342

How to translate a Select with uuid to plpgsql in java

1 Answers