{"action":"created","issue":{"url":"https://api.github.com/repos/pgmodeler/pgmodeler/issues/1080","repository_url":"https://api.github.com/repos/pgmodeler/pgmodeler","labels_url":"https://api.github.com/repos/pgmodeler/pgmodeler/issues/1080/labels{/name}","comments_url":"https://api.github.com/repos/pgmodeler/pgmodeler/issues/1080/comments","events_url":"https://api.github.com/repos/pgmodeler/pgmodeler/issues/1080/events","html_url":"https://github.com/pgmodeler/pgmodeler/issues/1080","id":303704101,"number":1080,"title":"Validation fails on imported table with user defined function","user":{"login":"yogesch","id":18548961,"avatar_url":"https://avatars1.githubusercontent.com/u/18548961?v=4","gravatar_id":"","url":"https://api.github.com/users/yogesch","html_url":"https://github.com/yogesch","followers_url":"https://api.github.com/users/yogesch/followers","following_url":"https://api.github.com/users/yogesch/following{/other_user}","gists_url":"https://api.github.com/users/yogesch/gists{/gist_id}","starred_url":"https://api.github.com/users/yogesch/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/yogesch/subscriptions","organizations_url":"https://api.github.com/users/yogesch/orgs","repos_url":"https://api.github.com/users/yogesch/repos","events_url":"https://api.github.com/users/yogesch/events{/privacy}","received_events_url":"https://api.github.com/users/yogesch/received_events","type":"User","site_admin":false},"labels":[{"id":12305873,"url":"https://api.github.com/repos/pgmodeler/pgmodeler/labels/bug","name":"bug","color":"e11d21","default":true}],"state":"open","locked":false,"assignee":null,"assignees":[],"milestone":{"url":"https://api.github.com/repos/pgmodeler/pgmodeler/milestones/21","html_url":"https://github.com/pgmodeler/pgmodeler/milestone/21","labels_url":"https://api.github.com/repos/pgmodeler/pgmodeler/milestones/21/labels","id":2743562,"number":21,"title":"v0.9.1","description":"","creator":{"login":"rkhaotix","id":2205476,"avatar_url":"https://avatars0.githubusercontent.com/u/2205476?v=4","gravatar_id":"","url":"https://api.github.com/users/rkhaotix","html_url":"https://github.com/rkhaotix","followers_url":"https://api.github.com/users/rkhaotix/followers","following_url":"https://api.github.com/users/rkhaotix/following{/other_user}","gists_url":"https://api.github.com/users/rkhaotix/gists{/gist_id}","starred_url":"https://api.github.com/users/rkhaotix/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/rkhaotix/subscriptions","organizations_url":"https://api.github.com/users/rkhaotix/orgs","repos_url":"https://api.github.com/users/rkhaotix/repos","events_url":"https://api.github.com/users/rkhaotix/events{/privacy}","received_events_url":"https://api.github.com/users/rkhaotix/received_events","type":"User","site_admin":false},"open_issues":11,"closed_issues":10,"state":"open","created_at":"2017-09-04T17:42:14Z","updated_at":"2018-03-09T11:51:05Z","due_on":null,"closed_at":null},"comments":1,"created_at":"2018-03-09T02:40:04Z","updated_at":"2018-03-09T12:18:35Z","closed_at":null,"author_association":"NONE","body":"In postgres, I first create a table and then I create a user defined function (which takes the table itself as the input argument type). Everything works nicely in postgres, but after importing the model to pgmodeler, it fails to validate with the error:\r\n\r\n>  Could not execute the SQL command.\r\n>  Message returned: `ERROR:  type \"person\" already exists\r\n\r\nIt seems that pgmodeler is trying to create a custom type \"person\" in this case (in the model objects sidebar, there is an entry \"person\" under type). So obviously it will fail because the type/table \"person\" already exists. It is possible to fix the validation problem by simply deleting the entry under type. But then exporting fails because it is not possible to drop type \"person\" when the table \"person\" exists. Oddly enough, if one creates the same function definition within pgmodeler, there is no such problem, but after exporting and reimporting the model the problem is back. \r\n\r\nSo this creates a usability issue where it is not possible to do export/import operations on models which have user defined functions. So any way to work around this would be most appreciated. I remain available for any followup queries. \r\n\r\nTo reproduce:\r\nIn postgres, create the following table and function. Then import the model into pgmodeler and validate. \r\n \r\n```\r\nCREATE TABLE person \r\n  ( \r\n    id int PRIMARY KEY, \r\n    first_name text, \r\n    last_name text NOT NULL \r\n  ); \r\n\r\nINSERT INTO person \r\n  VALUES \r\n    (1, 'John', 'Smith'), \r\n    (2, 'Jane', 'Doe'), \r\n    (3, NULL, 'Prince'); \r\n\r\nCREATE FUNCTION display_name(rec person) \r\n  RETURNS text \r\n  STABLE \r\n  LANGUAGE SQL \r\n  COST 5 \r\nAS $$ \r\n  SELECT \r\n    CASE \r\n      WHEN $1.first_name IS NULL THEN '' \r\n      ELSE $1.first_name || ' ' \r\n    END || $1.last_name; \r\n$$; \r\n\r\nSELECT p.id, p.display_name FROM person p;\r\n\r\n```\r\n\r\n"},"comment":{"url":"https://api.github.com/repos/pgmodeler/pgmodeler/issues/comments/371797693","html_url":"https://github.com/pgmodeler/pgmodeler/issues/1080#issuecomment-371797693","issue_url":"https://api.github.com/repos/pgmodeler/pgmodeler/issues/1080","id":371797693,"user":{"login":"yogesch","id":18548961,"avatar_url":"https://avatars1.githubusercontent.com/u/18548961?v=4","gravatar_id":"","url":"https://api.github.com/users/yogesch","html_url":"https://github.com/yogesch","followers_url":"https://api.github.com/users/yogesch/followers","following_url":"https://api.github.com/users/yogesch/following{/other_user}","gists_url":"https://api.github.com/users/yogesch/gists{/gist_id}","starred_url":"https://api.github.com/users/yogesch/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/yogesch/subscriptions","organizations_url":"https://api.github.com/users/yogesch/orgs","repos_url":"https://api.github.com/users/yogesch/repos","events_url":"https://api.github.com/users/yogesch/events{/privacy}","received_events_url":"https://api.github.com/users/yogesch/received_events","type":"User","site_admin":false},"created_at":"2018-03-09T12:18:35Z","updated_at":"2018-03-09T12:18:35Z","author_association":"NONE","body":"Thank you, after dropping the type I can now validate the model. \r\n\r\nBut after dropping the type in the model, if I try to do a diff with the server db, the diff has a command that looks like \"drop type ...\". So when I try to apply that diff, the server throws an error saying that the type cannot be dropped when the table still exists. \r\n\r\nSo the workaround I use now is to simply delete the function from pgmodeler, and recreate (easy enough to copy paste) it. "}}
{"actor":{"display_login":"yogesch"}}