Thread: plpython/python string formatting

I've just started playing with plpython but ran into a issue when I
was trying to use standard python string formatting to generate a SQL
string for example:
s = "EXECUTE format('CREATE INDEX %s ON %s USING (column_name)' %
(index_name, table_name))"

but plpython then tried to run the EXECUTE instead of just setting
variable 's'. Why does this happen?

Ted

On 8/16/22 16:42, Ted Toth wrote:
> I've just started playing with plpython but ran into a issue when I
> was trying to use standard python string formatting to generate a SQL
> string for example:
> s = "EXECUTE format('CREATE INDEX %s ON %s USING (column_name)' %
> (index_name, table_name))"
> 
> but plpython then tried to run the EXECUTE instead of just setting
> variable 's'. Why does this happen?

Best guess is you are seeing cross platform collision with:

https://www.postgresql.org/docs/current/functions-string.html#FUNCTIONS-STRING-FORMAT

Why not?:

s = format('CREATE INDEX %s ON %s USING (column_name)' %
(index_name, table_name))


> 
> Ted
> 
> 


-- 
Adrian Klaver
adrian.klaver@aklaver.com

On 8/16/22 16:42, Ted Toth wrote:
> I've just started playing with plpython but ran into a issue when I
> was trying to use standard python string formatting to generate a SQL
> string for example:
> s = "EXECUTE format('CREATE INDEX %s ON %s USING (column_name)' %
> (index_name, table_name))"
> 
> but plpython then tried to run the EXECUTE instead of just setting
> variable 's'. Why does this happen?


Or if you want to correctly quote the identifiers something like:

create table test_tbl(id integer);

DO $$
     index_name = 'test'
     table_name = 'test_tbl'
     s = 'CREATE INDEX "' + index_name + '" ' +  'ON "' + table_name + 
'" (id)'
     plpy.notice(s)
     plpy.execute(s)
$$ LANGUAGE plpythonu;


NOTICE:  CREATE INDEX "test" ON "test_tbl" (id)
DO

\d test_tbl
               Table "public.test_tbl"
  Column |  Type   | Collation | Nullable | Default
--------+---------+-----------+----------+---------
  id     | integer |           |          |
Indexes:
     "test" btree (id)


> 
> Ted
> 
> 


-- 
Adrian Klaver
adrian.klaver@aklaver.com