proposal: doc: simplify examples of dynamic SQL
От | Pavel Stehule |
---|---|
Тема | proposal: doc: simplify examples of dynamic SQL |
Дата | |
Msg-id | CAFj8pRA58o3fD23ruWDjuTQaF=iKukgJvkBosmfMgebOTnm2wA@mail.gmail.com обсуждение исходный текст |
Ответы |
Re: proposal: doc: simplify examples of dynamic SQL
|
Список | pgsql-hackers |
Hi
There are few less readable examples of dynamic SQL in plpgsql doc
like:
EXECUTE 'SELECT count(*) FROM '
|| tabname::regclass
|| ' WHERE inserted_by = $1 AND inserted <= $2'
INTO c
USING checked_user, checked_date;
or
EXECUTE 'UPDATE tbl SET '
|| quote_ident(colname)
|| ' = $'
|| newvalue
|| '$ WHERE key = '
|| quote_literal(keyvalue);
We can show a examples based on "format" function only:
EXECUTE format('SELECT count(*) FROM %I'
' WHERE inserted_by = $1 AND inserted <= $2',
tabname)
INTO c
USING checked_user, checked_date;
or
EXECUTE format('UPDATE tbl SET %I = newvalue WHERE key = %L',
colname, keyvalue)
or
EXECUTE format('UPDATE tbl SET %I = newvalue WHERE key = $1',
colname)
USING keyvalue;
A old examples are very instructive, but little bit less readable and maybe too complex for beginners. There are few less readable examples of dynamic SQL in plpgsql doc
like:
EXECUTE 'SELECT count(*) FROM '
|| tabname::regclass
|| ' WHERE inserted_by = $1 AND inserted <= $2'
INTO c
USING checked_user, checked_date;
or
EXECUTE 'UPDATE tbl SET '
|| quote_ident(colname)
|| ' = $'
|| newvalue
|| '$ WHERE key = '
|| quote_literal(keyvalue);
We can show a examples based on "format" function only:
EXECUTE format('SELECT count(*) FROM %I'
' WHERE inserted_by = $1 AND inserted <= $2',
tabname)
INTO c
USING checked_user, checked_date;
or
EXECUTE format('UPDATE tbl SET %I = newvalue WHERE key = %L',
colname, keyvalue)
or
EXECUTE format('UPDATE tbl SET %I = newvalue WHERE key = $1',
colname)
USING keyvalue;
В списке pgsql-hackers по дате отправления: