Re: ORDER BY with plpgsql parameter

On Wed, 2 Jun 2004, Thomas Schoen wrote:

> > On Wed, 2 Jun 2004, Thomas Schoen wrote:
> > > > You need to use the FOR-IN-EXECUTE style of query. That way
> > > > you can use any string you want (including text passed in as
> > > > a parameter) to build the query inside the function ...
> > >
> > > that is what i want to avoid. (i wrote that in my first mail)
> > > My question was about why it is not possible to do it like this:
> > > ....ORDER BY $1
> >
> > One problem is that doing the above as column name would make the $1 have
> > a different meaning from its use in other places like where clauses (where
> > it acts like a bound parameter).
>
> I know what you mean.
> I'm aware of this problem. I tired to quote the parameter using quote_ident
> functions which did not work either.
> I do not unserstand the logic behind parameters used in plpgsql-functions.
> I don't know how they are bound inside the database-logic.

AFAIK it's similar to PREPARE/EXECUTE. You have a number of arguments
whose values are substituted in as literals in those positions.

> > Apart from the quoting issue, I'm also not sure how it would be any
> > different from for-in-execute in any case.
> Maybe thats just my preference.
> I don't like the "build a string to interpret" kind of code.
> But it would be interesting to know if their are any performance disadvantages
> when using the "for in execute".

It does replan the query. But you would need to presumably replan if you
were changing the order by column anyway so I'm not sure that allowing
order by $1 to mean order by an expression stored as text in $1 would
realistically have any different performance characteristics than the
execute case.

> My experience of using "for in execute" was so far that it is much slower than
> doing it the direct way - and way slower than using sql-functions instead of
> plpgsql-functions.
> Maybe that experience was subjective.

I've seen it both ways, it depends on alot of factors.