Re: AW: Re: [GENERAL] Query caching

On Wed, 8 Nov 2000, Christof Petig wrote:

> Karel Zak wrote:
>
> > > What about parameters? Normally you can prepare a statement and execute it
> >
> > We have in PG parameters, see SPI, but now it's used inside backend only
> > and not exist statement that allows to use this feature in be<->fe.
>
> Sad. Since ecpg would certainly benefit from this.
>
> > > using different parameters. AFAIK postgres' frontend-backend protocol is not
> > > designed to take parameters for statements (e.g. like result presents
> > > results). A very long road to go.
> > > By the way, I'm somewhat interested in getting this feature in. Perhaps it
> > > should be part of a protocol redesign (e.g. binary parameters/results).
> > > Handling endianness is one aspect, floats are harder (but float->ascii->float
> > > sometimes fails as well).
> >
> > PREPARE <name> AS <query>
> > [ USING type, ... typeN ]
> > [ NOSHARE | SHARE | GLOBAL ]
> >
> > EXECUTE <name>
> > [ INTO [ TEMPORARY | TEMP ] [ TABLE ] new_table ]
> > [ USING val, ... valN ]
> > [ NOSHARE | SHARE | GLOBAL ]
> >
> > DEALLOCATE PREPARE
> > [ <name> [ NOSHARE | SHARE | GLOBAL ]]
> > [ ALL | ALL INTERNAL ]
> >
> > An example:
> >
> > PREPARE chris_query AS SELECT * FROM pg_class WHERE relname = $1 USING text;
>
> I would prefer '?' as a parameter name, since this is in the embedded sql standard
> (do you have a copy of the 94 draft? I can mail mine to you?)

This not depend on query cache. The '$n' is PostgreSQL query parametr
keyword and is defined in standard parser. The PREPARE statement not parsing
query it's job for standard parser.

> Also the standard says a whole lot about guessing the parameter's type.
>
> Also I vote for ?::type or type(?) or sql's cast(...) (don't know it's syntax)
> instead of abusing the using keyword.

The postgresql executor expect types of parametrs in separate input (array).
I not sure how much expensive/executable is survey it from query.

> > EXECUTE chris_query USING 'pg_shadow';
>
> Great idea of yours to implement this! Since I was thinking about implementing a
> more decent schema for ecpg but had no mind to touch the backend and be-fe
> protocol (yet).
> It would be desirable to do an 'execute immediate using', since using input
> parameters would take a lot of code away from ecpg.

By the way, PREPARE/EXECUTE is face only. More interesting in this period is
query-cache-kernel. SQL92 is really a little unlike my PREPARE/EXECUTE.

Karel