SoftTree Technologies SoftTree Technologies
Technical Support Forums
RegisterSearchFAQMemberlistUsergroupsLog in
SQL Assistant Schema compare issue

 
Reply to topic    SoftTree Technologies Forum Index » SQL Assistant View previous topic
View next topic
SQL Assistant Schema compare issue
Author Message
LonelyWalker



Joined: 16 Oct 2015
Posts: 18
Country: Russian Federation

Post SQL Assistant Schema compare issue Reply with quote
This is very old issues, that i'm fixing again and again...
1) Options -> Schema Compare -> PostgreSQL -> Functions
what is this weird language 'plsql'? And second, you see functions which `returns SETOF record` (or SETOF some_type) as `returns record`!!! And this bug translated to patch generator (so it generates wrong script)... These bugs are hidden and i had some unpleasant problems with them.
Please, use standart functions instead of self-made:

Code:
SELECT
    pn.nspname AS "Schema name",
    pp.proname || '(' || pg_catalog.pg_get_function_identity_arguments(pp.oid) || ')' AS "Function specific name",
    'Function' AS "Target type",
    pg_catalog.pg_get_function_result(pp.oid) AS "Returns",
    pp.proname AS "Function name__HIDDEN",
    pl.lanname AS "Language",
    pp.prosrc  AS "Definition__NAME_REFERENCE"
FROM pg_catalog.pg_proc AS pp
    JOIN pg_catalog.pg_namespace AS pn
        ON pn.oid = pp.pronamespace
    JOIN pg_catalog.pg_language AS pl
        ON pl.oid = pp.prolang AND pl.lanname IN ('sql', 'plpgsql','plpythonu')   
WHERE ('$SCHEMA_NAME$' = '' OR pn.nspname = '$SCHEMA_NAME$')
ORDER BY pp.proname


2) Add ordering to Trigger's event_manipulation. Because it times to time show differences like this: `INSERT or UPDATE` <> 'UPDATE or INSERT'
Code:
   string_agg(distinct event_manipulation, ' OR ')   AS "Event manipulation",
for min version 9.1 and
Code:
   array_to_string(ARRAY(
      SELECT event_manipulation::CHAR VARYING FROM information_schema.triggers t1
      WHERE t1."trigger_schema" = t."trigger_schema" AND t1."trigger_name" = t."trigger_name"
      order by event_manipulation
   ), ' OR ')         AS "Event manipulation",
for min. version 7

Thank you for attention.
Wed Feb 10, 2016 8:58 am View user's profile Send private message
SysOp
Site Admin


Joined: 26 Nov 2006
Posts: 7838

Post Reply with quote
Thank you, very much for reporting this issue. I'm going to log it in our issue tracking system and I hope it will be fixed soon.
Wed Feb 10, 2016 10:16 am View user's profile Send private message
LonelyWalker



Joined: 16 Oct 2015
Posts: 18
Country: Russian Federation

Post Reply with quote
Okay, that's better. But...
Here is next issues.

While compare tables ALL columns are NOT NULL. Please, remember about case sensitive equals.
Code:

    CASE is_nullable
       WHEN 'YES' THEN 'NULL'
        ELSE 'NOT NULL'
    END          AS "Nullable",

or even
Code:

    CASE upper(is_nullable)
       WHEN 'YES' THEN 'NULL'


Next. Arguments. The same issue as previous:

Code:
pp.proname || '(' || pg_catalog.pg_get_function_identity_arguments(pp.oid) || ')' AS "Function specific name",


Aggregate Functions.

Code:
pp.proname || '(' || pg_catalog.pg_get_function_identity_arguments(pp.oid) || ')' AS "Aggregate specific name",


Thank you for attention.
Thu Feb 25, 2016 2:19 am View user's profile Send private message
SysOp
Site Admin


Joined: 26 Nov 2006
Posts: 7838

Post Reply with quote
Thank you so much. I have logged all reported issues. Hope they will be corrected soon.
Thu Feb 25, 2016 2:35 am View user's profile Send private message
Display posts from previous:    
Reply to topic    SoftTree Technologies Forum Index » SQL Assistant All times are GMT - 4 Hours
Page 1 of 1

 
Jump to: 
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum


 

 

Powered by phpBB © 2001, 2005 phpBB Group
Design by Freestyle XL / Flowers Online.