Issue with the pgAdmin create script for the function wrapped with edbwrap (RM #3676)

[dpage]

Issue migrated from Redmine: https://redmine.postgresql.org/issues/3676 Originally created by Fahar Abbas at 2018-09-27 11:53:26 UTC.

I am able to reproduce the issue raised by support team:

---

Tested Server: EPAS 11

Windows 2012 R2 64

Build

https://www.postgresql.org/ftp/pgadmin/pgadmin4/snapshots/2018-09-27/

Steps:

1. create database wrapper and create schema elmoservice

2. copy following content on function_source.sql:

CREATE OR REPLACE FUNCTION elmoservice.elm_lokirivi ( i_elmo_taulunimi text, i_elmo_tauluriviid text, i_lisatiedot text, i_virhe text, i_status integer, i_pernimi text ) RETURNS text LANGUAGE 'plpgsql' AS $BODY$

DECLARE v_tunnus text; v_ss text; v_port text; v_db text; v_host text; v_conn_str text; v_query text; v_i text; v_linkkivirhe text; v_virhe text;

BEGIN

v_linkkivirhe := '0'; v_virhe := '0';

SELECT arvo INTO v_port FROM metamgr.yx_ohjaus WHERE sovel_sovellus = 'ELMOSERVICE' AND koodi = 'ELMOSERVICE_PO';

SELECT arvo INTO v_db FROM metamgr.yx_ohjaus WHERE sovel_sovellus = 'ELMOSERVICE' AND koodi = 'ELMOSERVICE_DB';

SELECT arvo INTO v_tunnus FROM metamgr.yx_ohjaus WHERE sovel_sovellus = 'ELMOSERVICE' AND koodi = 'ELMOSERVICE';

SELECT arvo || 'huhaatapa' INTO v_ss FROM metamgr.yx_ohjaus WHERE sovel_sovellus = 'ELMOSERVICE' AND koodi = 'ELMOSERVICE_SA';

SELECT arvo INTO v_host FROM metamgr.yx_ohjaus WHERE sovel_sovellus = 'ELMOSERVICE' AND koodi = 'ELMOSERVICE_OS';

-- Muodostetaan yhteyden tiedot ja funktiokutsu muuttujiin. v_conn_str:='port=' || v_port || ' dbname=' || v_db || ' host=' || v_host || ' user=' || v_tunnus || ' password=' || v_ss;

v_query := 'SELECT * FROM elmoservice.elm_lokirivi_x ( ' || quote_nullable(i_elmo_taulunimi) || ',' || quote_nullable(i_elmo_tauluriviid) || ',' || quote_nullable(i_lisatiedot) || ',' || quote_nullable(i_virhe) || ',' || i_status || ',' || quote_nullable(i_pernimi) || ')';

-- Suljetaan valmiiksi avoinna oleva dblink-yhteys IF elmoservice.elm_dblinkloytyy('lokitus') THEN PERFORM * FROM dblink_disconnect('lokitus');

END IF;

PERFORM FROM dblink_connect_u('lokitus', v_conn_str); SELECT INTO v_linkkivirhe FROM dblink('lokitus', v_query) AS p (v_linkkivirhe text); PERFORM * FROM dblink_disconnect('lokitus');

-- Heitetään virheilmoitus, jos kutsuttava funktio on mennyt virheeseen. IF v_linkkivirhe != '0' THEN RAISE EXCEPTION USING ERRCODE = 50001; END IF;

-- RETURN v_virhe;

EXCEPTION WHEN SQLSTATE '50001' THEN RAISE notice 'v_linkkivirhe: %', v_linkkivirhe; v_virhe := 'Linkkiin liittyvä virhe 50001 funktiosta elm_lokirivi(): ' || v_linkkivirhe;

RETURN v_virhe;

WHEN OTHERS THEN RAISE notice 'Virhe funktiosta elm_lokirivi'; RAISE notice 'SQLERRM: %', SQLERRM; RAISE notice 'SQLSTATE: %', SQLSTATE;

v_virhe := 'Virhe funktiosta elm_lokirivi' || 'SQLERRM: %', SQLERRM || 'SQLSTATE: %', SQLSTATE;

IF elmoservice.elm_dblinkloytyy('lokitus') THEN PERFORM * FROM dblink_disconnect('lokitus'); END IF;

RETURN v_virhe;

END; $BODY$;

3. go into C:\Program Files\edb\as11\bin folder
4. run the command

C:\Program Files\edb\as11\bin>edbwrap.exe -i function_source.sql -o function_wrapper.output

EDB*Wrap Utility: Release 11.0.3

Copyright (c) 2004-2018, EnterpriseDB Corporation. All Rights Reserved.

Using encoding WIN1252 for input Processing function_source.sql to function_wrapper.output

5.connect to wrapper database wrapper=# \i function_wrapper.output CREATE FUNCTION

6. Take a backup and it's successful.

7. Just Click on function and Click on create script

8. Execute this command just rename the wrapper function name with "elm_lokirivi1" and create in sql file with name "function_wrapper_pgadmin4.output name"

9. Now take a backup

10. failed with exit code 1 invalid wrapped string

11. now drop the DROP FUNCTION elmoservice.elm_lokirivi1

12. take a backup of database and it's successful.

Both function_wrapper.output(shared by user) and function_wrapper_pgadmin4.output.txt(copied contact from create script of pgAdmin4)

[dpage]

Attachment migrated from Redmine: https://redmine.postgresql.org/attachments/download/3307 Originally created by Fahar Abbas at 2018-09-27 11:48:22 UTC.

https://pgadmin-archive.postgresql.org/redmine/3676/3307-function_wrapper.output

[dpage]

Attachment migrated from Redmine: https://redmine.postgresql.org/attachments/download/3308 Originally created by Fahar Abbas at 2018-09-27 11:49:26 UTC.

https://pgadmin-archive.postgresql.org/redmine/3676/3308-function_wrapper_pgadmin4.output.txt

[dpage]

Attachment migrated from Redmine: https://redmine.postgresql.org/attachments/download/3309 Originally created by Fahar Abbas at 2018-09-27 11:55:15 UTC.

https://pgadmin-archive.postgresql.org/redmine/3676/3309-function_source.sql

[dpage]

Comment migrated from Redmine: https://redmine.postgresql.org/issues/3676#note-4 Originally created by Fahar Abbas at 2018-09-27 11:55:37 UTC.

function_source.sql file is also attached.

Redmine ticket header update:

Name	Old Value	New Value
Attachment added		function_source.sql

[dpage]

Comment migrated from Redmine: https://redmine.postgresql.org/issues/3676#note-5 Originally created by Harshal Dhumal at 2018-09-27 12:03:15 UTC.

Redmine ticket header update:

Name	Old Value	New Value
Subject changed	796491 - Issue with the pgAdmin create script for the function wrapped with edbwrap	Issue with the pgAdmin create script for the function wrapped with edbwrap

[dpage]

Comment migrated from Redmine: https://redmine.postgresql.org/issues/3676#note-6 Originally created by Khushboo Vashi at 2018-10-10 09:37:02 UTC.

Redmine ticket header update:

Name	Old Value	New Value
Tracker changed	Support	Bug

[dpage]

Comment migrated from Redmine: https://redmine.postgresql.org/issues/3676#note-7 Originally created by Khushboo Vashi at 2018-10-10 09:37:14 UTC.

Redmine ticket header update:

Name	Old Value	New Value
Sprint changed		EDB Sprint 25

[dpage]

Comment migrated from Redmine: https://redmine.postgresql.org/issues/3676#note-8 Originally created by Akshay Joshi at 2018-10-24 09:38:12 UTC.

Redmine ticket header update:

Name	Old Value	New Value
Sprint changed	EDB Sprint 25	EDB Sprint 26

[dpage]

Comment migrated from Redmine: https://redmine.postgresql.org/issues/3676#note-9 Originally created by Akshay Joshi at 2018-10-26 06:26:17 UTC.

Redmine ticket header update:

Name	Old Value	New Value
Status changed	New	In Progress
Assigned To changed		Akshay Joshi

[dpage]

Comment migrated from Redmine: https://redmine.postgresql.org/issues/3676#note-10 Originally created by Akshay Joshi at 2018-10-29 07:08:02 UTC.

Patch sent: https://www.postgresql.org/message-id/CANxoLDcOhEeem7oGLVjaxeJwT2_4uA4V6Oro_ViSSM19zLa9tQ@mail.gmail.com

[dpage]

Comment migrated from Redmine: https://redmine.postgresql.org/issues/3676#note-11 Originally created by Akshay Joshi at 2018-10-29 11:54:16 UTC.

Applied in changeset commit:696fbfb8b955cb55a6ef6ce4f7f53b82e0a619e9.

Redmine ticket header update:

Name	Old Value	New Value
Status changed	In Progress	In Testing
Done Ratio changed	100	90

[dpage]

Comment migrated from Redmine: https://redmine.postgresql.org/issues/3676#note-12 Originally created by Dave Page at 2018-10-29 11:54:45 UTC.

Redmine ticket header update:

Name	Old Value	New Value
Assigned To changed	Akshay Joshi
Fixed Version changed		3.5

[dpage]

Comment migrated from Redmine: https://redmine.postgresql.org/issues/3676#note-13 Originally created by Akshay Joshi at 2018-10-30 05:08:35 UTC.

Redmine ticket header update:

Name	Old Value	New Value
Assigned To changed		Akshay Joshi

[dpage]

Comment migrated from Redmine: https://redmine.postgresql.org/issues/3676#note-14 Originally created by Fahar Abbas at 2019-01-15 05:28:20 UTC.

This is also Verified in pgadmin4 4.1 Release and it's resolved.

Redmine ticket header update:

Name	Old Value	New Value
Status changed	In Testing	Resolved

[dpage]

Issue closed on Redmine.