Re: request for enhancement of protocol - Mailing list pgsql-hackers

On Sat, Nov 19, 2005 at 12:40:23PM +0100, Pavel Stehule wrote:
>
>
> >What do you mean? There are already 10 levels for elog, including five
> >levels of DEBUG. How many more do you want?
>
> sometimes I need show only some text. Now I get stack info.
> lighter elog ~ sending text, not. proc, stack info.

You could turn the log_error_verbosity down so it doesn't show stack
info.

> >The protocol already supports this and libpq does also. However, I
> >think that unless you are using async mode you may have difficulty
> >retrieving it. There's also a comment there about whether the backend
> >can actually do it, so maybe some work need to be done there.

Actually, I played with some functions in 8.1 and this is possible:

test=# create or replace function test(int4) returns setof unknown as
$$declare  a record;
begin  select into a * from pg_attribute limit 1; return next a;  return next (1,2,4);  return next ('hello','world');
returnnext 'plain string'; return; end$$ 
language plpgsql;
CREATE FUNCTION
test=# select * from test(1);                        test

---------------------------------------------------(1247,typname,19,-1,64,1,0,-1,-1,f,p,i,t,f,f,t,0)(1,2,4)(hello,world)plain
string
(4 rows)

Ok, not maybe the neatest way of doing it, but it works right now.

> >> 3. session (package) variables and calling procedures with OUT, INOUT in
> >> normal style, tj. stmt CALL. - heavy task, because I can write function
> >> a(IN int, IN int), and a(OUT int, OUT int) now. This is problem, and
> >need
> >> restriction.
> >
> >I can understand the CALL but what's the confusing between the two
> >functions a? One is a(1,2), the other is a().
>
> when I can use variables (in plpgsql now, in sql in future - package
> variables) I have to remember form of function. I can't to call a(@x1,
> @x2). Why. Caller don't know if I mean variant one or variant two. And I
> have to use nonstandard convension select into a(). Nonstandard in
> separation in and out variables. I prefere some restriction here.

Hmm, I searched the standard for package variable but couldn't find it.
Does the syntax have to do that? What if you have an INOUT parameter
and you want the output to go to a different place than the input.
Wouldn't:

SELECT INTO @x1, @x2 from a();
SELECT * from a(@x1,@x2);

be less ambiguous?

> SELECT works well if I expect scalar value. But if I expect table I have to
> use diff. form
> SELECT * FROM ... I see two modes of calling a) select - typed result, b)
> call - untyped result. For point a I have different requirements than for
> point b. And I see difference between statement call (clauses where, from,
> .....) and statement call. PostgreSQL don't support procedures now, only
> functions.

As pointed out above, PostgreSQL does support untyped results, just as
long as you don't try to pass it to any other functions.

> if you have to solve creating cross table for normal interactive using in
> console, you have two possibilities: 1. call stored procedure which
> generate temp wiew and user will do select from view, or procedure can
> create cursor and user will do select from cursor. But you can't do in one
> procedure now.

You mean a crosstab query like in contrib/tablefunc? That doesn't
require creating a view. But then, that may not be what you want.

> I spent some time for looking way for implementing this into plpgsql. I
> didn't find it. It's part of SPI too.

I imagine in SPI you could unpack the sets returned by functions
returing "unknown", but I havn't tried that.

Have a nice day,
--
Martijn van Oosterhout   <kleptog@svana.org>   http://svana.org/kleptog/
> Patent. n. Genius is 5% inspiration and 95% perspiration. A patent is a
> tool for doing 5% of the work and then sitting around waiting for someone
> else to do the other 95% so you can sue them.