proposal: doc: simplify examples of dynamic SQL

From: Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com>
To: PostgreSQL Hackers <pgsql-hackers(at)postgresql(dot)org>
Subject: proposal: doc: simplify examples of dynamic SQL
Date: 2014-10-02 11:51:30
Message-ID: CAFj8pRA58o3fD23ruWDjuTQaF=iKukgJvkBosmfMgebOTnm2wA@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: 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.

Opinions?

Regards

Pavel

Responses

Browse pgsql-hackers by date

  From Date Subject
Next Message Marko Tiikkaja 2014-10-02 12:12:29 Re: pgcrypto: PGP signatures
Previous Message Heikki Linnakangas 2014-10-02 11:47:33 Re: pgcrypto: PGP signatures