View Issue Details

IDProjectCategoryView StatusLast Update
0000811PostgreSQLGeneralpublic2020-02-23 08:00
Reportershirokov Assigned Tobarry  
PriorityhighSeveritymajorReproducibilityalways
Status closedResolutionfixed 
Fixed in Version8.0.0.1693.Beta 
Summary0000811: Postgre SQL database extractor errors
DescriptionFile 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$$
TagsNo tags attached.

Activities

shirokov

2015-04-20 15:52

administrator  

metadata.sql (22,784 bytes)

Issue History

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