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

2010/3/21 Tom Lane <tgl@sss.pgh.pa.us>:
> Dimitri Fontaine <dfontaine@hi-media.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
isnull 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 │ begin2 │   for i in 1..3 loop3 │     raise notice 'i=%', i/0;4 │   end loop;5 │ end;6 │
$function$ │ 
(11 rows)

regards
Pavel Stehule

>
>                        regards, tom lane
>