Re: Inline PL/pgSQL

On E, 2005-05-09 at 11:44 -0600, Jonah H. Harris wrote:
> Hey everyone,
>
> In addition to package support in plpgsql, it would be really handy to
> have inline plpgsql. Likewise, I think there are others who feel this
> way as-well.

Session variables is what I miss most.

> Years ago, Oracle merged PL/SQL with their normal SQL parser which
> allowed for inline PL/SQL.

Was that really that much time ago ? IIRC this was fanfared as one of
big advancements of Oracle 10.

> They did this because it was difficult to maintain two separate parsers.

Also they claimed that this cleared away some subtle differences in the
languages supported by SQL and pl/SQL.

> While this worked great for Oracle, it
> probably wouldn't really work as well for PostgreSQL because pgsql
> supports multiple procedural languages.

AFAIK Oracle also supports at least java, using a syntax somewhat
similar to ours.

> As for implementation, I think it would obviously be best to leave
> plpgsql on its own as a PL but maybe change BEGIN and DECLARE in the
> normal parser and have the system generate/execute a function on the
> fly. Or, maybe it would be better to integrate plpgsql. Or, I may just
> be crazy.

Just having $$ quoting and named arguments does most of what I need for
using functions from a command line. If pl/pgsql and perhaps even plain
sql get session variables, preferrably usable by both (and in future
accessible from other) pl-s that would cover most of my needs.

> Would anyone else ever benefit from inline functions? Does anyone have
> any ideas about implementation? Please shoot your opinions this way.

While I can imagine how to use a declarative language from procedural
one (pl/pgsql using sql) I have much harder time to imagine how to do
the opposite in a convevient way.

something like this ? :

select $$ inline scalar plpgsql func here $$, count(*)
from $$ inline set function here $$ sub
group by 1;

perhaps just supporting TEMP funcions should be enough ?

--
Hannu Krosing <hannu(at)tm(dot)ee>