fix to use ``pg_get_functiondef()``

[antonov-impulsm]

fix to use pg_get_functiondef() in "Scripts=>CREATE Script".

pg_get_functiondef() -- less wrong

[ghost]

Hi! Could you send this pull request as patch to pgadmin-hackers@postgresql.org?

[ghost]

BTW

pg_get_functiondef() -- less wrong

what's wrong with this function? :)

[antonov-impulsm]

@olshevskiy87 > what's wrong with this function? :)

ok. a few examples:

example "A"

write function:

CREATE FUNCTION test123()
 RETURNS TABLE(my_col text) 
 LANGUAGE plpgsql
AS $xxx$
BEGIN

my_col := 'abc';
RETURN NEXT;

my_col := 'def';
RETURN NEXT;

END;
$xxx$;

--SELECT * FROM test123();

pgAdmin3 transforming it to function:

-- Function: test123()

-- DROP FUNCTION test123();

CREATE OR REPLACE FUNCTION test123()
  RETURNS SETOF text AS
$BODY$
BEGIN

my_col := 'abc';
RETURN NEXT;

my_col := 'def';
RETURN NEXT;

END;
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100
  ROWS 1000;
ALTER FUNCTION test123()
  OWNER TO postgres;

and of course with error:

ERROR:  "my_col" is not a known variable
LINE 10: my_col := 'abc';
         ^

********** Error **********

ERROR: "my_col" is not a known variable
SQL state: 42601
Character: 129

example "B"

CREATE FUNCTION test234(arr text[] DEFAULT ARRAY['sss', 'ddd'])
 RETURNS SETOF text
 LANGUAGE plpgsql
AS $xxx$
DECLARE
  r record;
BEGIN

FOR r IN SELECT UNNEST(arr) LOOP
  RETURN NEXT r.unnest;
END LOOP;

END;
$xxx$;

--SELECT * FROM test234();

pgAdmin3 transforming it to function:

-- Function: test234(text[])

-- DROP FUNCTION test234(text[]);

CREATE OR REPLACE FUNCTION test234(arr text[] DEFAULT ARRAY['sss'::text)
  RETURNS SETOF text AS
$BODY$
DECLARE
  r record;
BEGIN

FOR r IN SELECT UNNEST(arr) LOOP
  RETURN NEXT r.unnest;
END LOOP;

END;
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100
  ROWS 1000;
ALTER FUNCTION test234(text[])
  OWNER TO postgres;

...with error:

ERROR:  syntax error at or near ")"
LINE 5: ...PLACE FUNCTION test234(arr text[] DEFAULT ARRAY['sss'::text)
                                                                      ^
********** Error **********

ERROR: syntax error at or near ")"
SQL state: 42601
Character: 137

other minor discomfort

words -- VOLATILE, COST 100, ROWS 1000 -- are extra (unnecessary, redundant).

[ghost]

ok. a few examples:

but this sql code was generated by pgadmin, isn't it? I thought you are talking about native postgres function pg_get_functiondef() ) I would advise you to copy the previous comment to pgadmin-hackers@postgresql.org

[antonov-impulsm]

but this sql code was generated by pgadmin, isn't it? I thought you are talking about native postgres function pg_get_functiondef()

sorry for my ambiguity message..

yes. my examples -- about pgadmin-generation (without pg_get_functiondef() )

I wanted to say: I do not know about bad sides of pg_get_functiondef() , but if they exists -- I think them less then bad sides of original-pgadmin-generation .

I would advise you to copy the previous comment to pgadmin-hackers@postgresql.org

yes. i copied (just now)

[repo-lockdown[bot]]

Thanks for your Pull Request! :smile: This repo on GitHub is just a mirror of our real git repositories though, and can't really handle PRs. :frowning: Hopefully you can redo the PR, and direct it to the git.postgresql.org repos? We have a developer guide, if that helps: https://wiki.postgresql.org/wiki/So,_you_want_to_be_a_developer%3F. If this was a PR for pgAdmin, please visit https://www.pgadmin.org/docs/pgadmin4/dev/submitting_patches.html.