View Issue Details
| ID | Project | Category | View Status | Date Submitted | Last Update |
|---|---|---|---|---|---|
| 0000811 | PostgreSQL | General | public | 2015-04-20 15:52 | 2020-02-23 08:00 |
| Reporter | shirokov | Assigned To | barry | ||
| Priority | high | Severity | major | Reproducibility | always |
| Status | closed | Resolution | fixed | ||
| Fixed in Version | 8.0.0.1693.Beta | ||||
| Summary | 0000811: Postgre SQL database extractor errors | ||||
| Description | File used to create a database (in annex): metadata.sql ============================================================================ DOMAINs: * (1) cannot extract the standard value of domains (CREATE DOMAIN ... DEFAULT ...) Ex: CREATE DOMAIN dm_numero integer default 0; RULEs: * (2) cannot extract the rules (CREATE RULE ...) Ex: CREATE RULE rl_teste_insert AS ON INSERT TO vi_teste DO INSTEAD INSERT INTO tb_teste (id, info) VALUES ( <http://new.id> new.id, <http://new.info> new.info); Obs 1: the command CREATE RULE is not say the squema name together a rule name, but together at object that rules belong. Sintax: CREATE RULE rule_name AS ON operation TO schema_name.object_name ... Obs 2: the same sintax must be use to command DROP RULE. Sintax: DROP RULE rule_name ON schema_name.object_name FUNCTIONs: * (5) is not extract the parameter that was before another parameter with operator “IN” at declare (ALTER FUNCTION) Ex 2: ALTER FUNCTION teste.fn_teste_in(IN iid integer, OUT oid integer, OUT oinfo character varying) OWNER TO postgres; - wrong return: ALTER FUNCTION teste.fn_teste_in(IN iid integer, OUT oid integer, OUT oinfo character varying) RETURNS SETOF record OWNER TO postgres; - Correct return: ALTER FUNCTION teste.fn_teste_in(IN iid integer, OUT oid integer, OUT oinfo character varying) OWNER TO postgres; * (6) commands CREATE FUNCTION that returns (RETURNS [SETOF] ...) a type (CREATE TYPE) is returning "SETOF ? datatype" Ex: CREATE FUNCTION teste.fn_teste_type(iid integer) RETURNS SETOF tp_teste ... so return CREATE FUNCTION teste.fn_teste_type(iid integer) RETURNS SETOF ? datatype ... -wrong return: CREATE FUNCTION teste.fn_teste_type(IN iid integer) RETURNS SETOF "teste.tp_teste" AS ... - correct return: CREATE FUNCTION teste.fn_teste_type(IN iid integer) RETURNS SETOF teste.tp_teste AS ... (need remove COMMA ” ) *(8) if you comparer, and create a syncronize script where the function body must be temporary empty, this command is create: CREATE OR REPLACE FUNCTION fn (p1 ... pn ) RETURNS ... AS $$ BEGIN SUSPEND; END$$ LANGUAGE 'plpgsql'; error: command SUSPEND doesn’t exist Solution is: leave the body function empty, like: $$ BEGIN END$$ OBS: is not corret use the command EXIT in this place, because has the error:” Error: Procedure fn: Invalid statement (? assignment)". Because that must be create empty, or with comments like: $$ BEGIN /*empty*/ END$$ | ||||
| Tags | No tags attached. | ||||
| Date Modified | Username | Field | Change |
|---|---|---|---|
| 2015-04-20 15:52 | shirokov | New Issue | |
| 2015-04-20 15:52 | shirokov | File Added: metadata.sql | |
| 2019-04-19 14:54 | barry | Project | Database Comparer => PostgreSQL |
| 2019-12-12 10:37 | barry | Assigned To | => barry |
| 2019-12-12 10:37 | barry | Status | new => resolved |
| 2019-12-12 10:37 | barry | Resolution | open => fixed |
| 2019-12-12 10:37 | barry | Fixed in Version | => 8.0.0.1693.Beta |
| 2020-02-23 08:00 | barry | Status | resolved => closed |
