Re: proposal: more practical view on function's source code

2010/3/21 Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>:
> Dimitri Fontaine <dfontaine(at)hi-media(dot)com> writes:
>> I'm not sure what better tool than what Pavel is proposing we already
>> have, though.
>
> We have quite decent features for localizing syntax errors in functions, eg
>
> regression=# create function foo(x int) returns int language plpgsql as $$
> begin
>  return 1/;
> end$$;
> ERROR:  syntax error at end of input
> LINE 3:   return 1/;
>                   ^
> regression=#
>
> What I think is called for is extending that approach to run-time
> errors.  plpgsql doesn't make any particular effort to provide that
> right now, but it easily could IMO.  Pavel's proposal is only of use to
> people using psql, which is not everyone --- and it seems pretty awkward
> to me even for psql users.

yes - it is just for psql users. I manage some database where I can
run only psql. I have to see some lines before and some lines after.
It is one argument.

second argument - current view is too wide, too long

some general support can be included in core

create or replace function pg_get_function_rows(oid)
returns table(n int, src text) as $$
begin
for src in select * from
unnest(string_to_array(pg_get_functiondef($1),e'\n'))
loop
if src like '%$function$%' then
return next;
if n is null then
n := 1;
else
n = null;
end if;
else
return next;
n := n + 1;
end if;
end loop;
return;
end;
$$ language plpgsql;
CREATE FUNCTION
Time: 236.426 ms

postgres=# select * from pg_get_function_rows(16385); n │
src
───┼─────────────────────────────────────────
│ CREATE OR REPLACE FUNCTION public.foo()
│ RETURNS void
│ LANGUAGE plpgsql
│ AS $function$
1 │ begin
2 │ for i in 1..3 loop
3 │ raise notice 'i=%', i/0;
4 │ end loop;
5 │ end;
6 │ $function$
│
(11 rows)

regards
Pavel Stehule

>
>                        regards, tom lane
>