Skip site navigation (1) Skip section navigation (2)

proposal: SQL parser integration to PL/pgSQL

From: Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com>
To: PostgreSQL Hackers <pgsql-hackers(at)postgresql(dot)org>
Subject: proposal: SQL parser integration to PL/pgSQL
Date: 2009-05-24 07:34:20
Message-ID: 162867790905240034v3b6383dmcddb0397d9a270bb@mail.gmail.com (view raw or flat)
Thread:
Lists: pgsql-hackers
==Steps==
1. add hook to analyser (transform stage) to substitute unknown
columnref by param - when analyser detect unknown columnref, then call
callback, that returns possible para node or NULL (when external
environment doesn't have a variable). Returned param should be typed
or unknown (for polymorphic params).

2. add special modes to sql parser:
* that allows identify unknown columnref, but don't try to identify
functions, operators - but SRF function's should be identified,
because generate values (same with relations).

* that allows identify potential conflict's identifiers (maybe hook on
transformColumnRef?)

3. with this we should ensure some levels of SQL validation in
PL/pgSQL function:

* Full with warnings [FWW]- this identify identifier's collision (SQL,
PL/pgSQL),
* Full without warnings [FWOW] - this identify unknown functions,
unknown relations,
* Syntax - this identify correct syntax and unknown relation (ignore
check functions and operators),
* None - some "heuristic" minimalistic validation (like current) isn't
compatible and isn't usable.

4. For validation function user could to choose validation's level (GUC1),
5. For run - system is in [FWW] or [FWOW] mode (GUC2) - plans are
generate in compilation time for first run (?? we have to store
parseTree to protect from double query parsing).

==Issues==
* invasive patch - any relation changes (alter table add [drop]
column) should need "recompilation" (not only plan invalidation), new
dependency
* maybe little bit slower first run plpgsql functions,
* some new bugs
* developers have to change some habits - for full validatin should be
necessary creating "skeleton" functions.

==Benefits==
* identifier collisions should be detected clearly and early,
* SQL statements should be fully checked,
* some bugs will be displayed with clean messaage,
* more natural behave for people from Oracle, DB2
* allows named params for SQL

Note: this proposal is related to
http://archives.postgresql.org/pgsql-patches/2007-11/msg00253.php

Notes, comments?

Regards
Pavel Stehule

Responses

pgsql-hackers by date

Next:From: Gevik BabakhaniDate: 2009-05-24 09:32:37
Subject: Re: information_schema.columns changes needed for OLEDB
Previous:From: Pavel StehuleDate: 2009-05-24 04:31:19
Subject: Re: generic options for explain

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group