export sql script for function #163

Closed
fte1965 opened this Issue Mar 15, 2013 · 3 comments

2 participants

@fte1965

I define function -> public.fnv_tle_alltodate(in timestamp,out text)
and set execute premissions for role nsat_users
pgmodeler generate next sql script:
GRANT EXECUTE
ON FUNCTION public.fnv_tle_alltodate(timestamp,text)
TO ncsat_users;
this statement causes error when executed by psql
pgmodeler must generate:
GRANT EXECUTE
ON FUNCTION public.fnv_tle_alltodate(in timestamp, out text)
TO ncsat_users;

And one more problem....
pgmodeler must insert this command into script before generate function section
set check_function_bodies to false;

because otherwise you must determine order how to create a function,views and tables, which is impossible in large projects

@fte1965

and one more ... default value of parameters not generated in sql script....

@rkhaotix rkhaotix added a commit that referenced this issue Mar 15, 2013
@rkhaotix rkhaotix Fixed IN/OUT keywords on functions signature (issue #163)
Major changes on SQL code generation/export. Introduced a token to
help export process to identify the end of each DDL command.

Minor improvements on role editing form.

Fixed a bug on ModelExportForm that was truncating commands wrongly.
e8cb41a
@rkhaotix
PostgreSQL Database Modeler member

Hello @fte1965 ,

I think this problem is solved now!

Probably your model will not load anymore and you have to made some manual changes on the .dbm file.

if you have a function declared with some parameters which has attributes in="true" or out="true":

<function name="func_name"
        window-func="false"
        returns-setof="false"
        behavior-type="CALLED ON NULL INPUT"
        function-type="IMMUTABLE"
        security-type="SECURITY INVOKER"
        execution-cost="10"
        row-amount="0">
    <schema name="public"/>
    <comment>comment on function</comment>
    <language name="plpgsql"/>
    <return-type>
    <type name="interval"/>
    </return-type>
    <parameter name="p1" in="true">
        <type name="integer"/>
    </parameter>
    <parameter name="p1" in="false" out="true">
        <type name="integer"/>
    </parameter>
       <definition> ...

It's current signature (on dbm file) will be: public.func_name(integer,integer)

When loading your model file pgModeler will complain about the absence of the above function, this because the signature is not generated without IN/OUTs anymore and you will have to fix each objects that references the above function updating the reference tag from:

<function signature="public.func_name(integer,integer)"/>

to

<function signature="public.func_name(IN integer,OUT integer)"/>

NOTE: the IN/OUT keywords MUST be uppercase.

Let me know if something goes wrong!

@fte1965

all right.
I am delete tag in my .dbm file and recreate him from pgmodeler

@fte1965 fte1965 closed this Mar 15, 2013
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment