On 21.8.2013 15:18, Dave Page wrote:
So the value is shown correctly in the query tool when you create the
function, but when the SQL is reverse engineered again, it gets changed?
Something is changed during storing function definition to db. You may
open existing, properly looking function in sql editor, change EOLs to
MAC, and hit F5. After that, refresh function in object tree - the
function will contain quotes instead of $body$.
FYI, using the single quotes is valid syntax, but a *quick* look at
the code indicates that pgAdmin only does it if it thinks the server
is 7.4.x or older (which didn't support dollar quoting). If it's
getting that check wrong, I'd be very surprised as that would cause
all manner of things to break horribly.
We have 8.4 server. Maybe version comparison fails.
I'm attaching the log for following operations:
- refreshing function in tree (select function and hit F5)
- open the function (properly looking) function by using Scripts/Create
script from context menu (before it, EOLs has been set to Unix format)
- changing EOL format to MAC
- storing function by hitting F5
- refreshing function in tree (select function and hit F5)
Ha... To fix the function appearance, it is enough to switch back to
Unix EOLs. Replacing quotes by $$ is not needed.
Thanx
MK
2013-08-21 15:35:34 STATUS : Refreshing function bonus_5_ma_narok...
2013-08-21 15:35:34 QUERY : Set query (****): SELECT pr.oid, pr.xmin, pr.*,
format_type(TYP.oid, NULL) AS typname, typns.nspname AS typnsp, lanname,
proargnames, pg_get_expr(proargdefaults, 'pg_catalog.pg_class'::regclass) AS
proargdefaultvals, pronargdefaults, proconfig, pg_get_userbyid(proowner)
as funcowner, description
FROM pg_proc pr
JOIN pg_type typ ON typ.oid=prorettype
JOIN pg_namespace typns ON typns.oid=typ.typnamespace
JOIN pg_language lng ON lng.oid=prolang
LEFT OUTER JOIN pg_description des ON des.objoid=pr.oid
WHERE pr.oid=471599650::oid
ORDER BY proname
2013-08-21 15:35:34 QUERY : Set query (****): SELECT oid, format_type(oid,
NULL) AS typname FROM pg_type
2013-08-21 15:35:34 INFO : Deleting Function bonus_5_ma_narok for refresh
2013-08-21 15:35:34 INFO : Replacing with new node Function bonus_5_ma_narok
for refresh
2013-08-21 15:35:34 INFO : Displaying properties for Function bonus_5_ma_narok
2013-08-21 15:35:34 STATUS : Refreshing function bonus_5_ma_narok... (0.09 secs)
2013-08-21 15:35:36 INFO : Opening connection with connection string:
host='****' dbname='****' user='****' port=**** application_name='pgAdmin III -
Query Tool'
2013-08-21 15:35:36 QUERY : Scalar query (****): SELECT version();
2013-08-21 15:35:36 QUERY : Query result: PostgreSQL 8.4.6 on
x86_64-redhat-linux-gnu, compiled by GCC gcc (GCC) 4.1.2 20080704 (Red Hat
4.1.2-48), 64-bit
2013-08-21 15:35:36 QUERY : Set query (****): SET DateStyle=ISO;
SET client_min_messages=notice;
SELECT oid, pg_encoding_to_char(encoding) AS encoding, datlastsysoid
FROM pg_database WHERE oid = 471599280
2013-08-21 15:35:36 INFO : Setting client_encoding to 'UNICODE'
2013-08-21 15:35:36 SCRIPT : Application created
2013-08-21 15:35:43 QUERY : Thread query (****): -- Function:
bonus_5_ma_narok(integer, numeric)
-- DROP FUNCTION bonus_5_ma_narok(integer, numeric);
CREATE OR REPLACE FUNCTION bonus_5_ma_narok(_idk integer, _castka numeric)
RETURNS integer AS
$BODY$
DECLARE
_id integer;
_min_vklad numeric;
_internet integer;
BEGIN
SELECT tab_klient.internet INTO _internet
FROM tab_klient
WHERE tab_klient.id_klient=$1;
IF abs(_internet)<>1 THEN return 0; END IF;
SELECT szn_bonus.id, szn_bonus.min_vklad INTO _id, _min_vklad
FROM szn_bonus
WHERE szn_bonus.id_typ=5 AND szn_bonus.aktivni=1;
IF _id IS NULL OR _castka<_min_vklad THEN return 0; ELSE _id=NULL; END IF;
SELECT tab_bonusy.id INTO _id
FROM tab_bonusy
WHERE tab_bonusy.id_klient=$1
AND tab_bonusy.id_typ_bonus=5;
IF _id IS NULL THEN return 1; ELSE return 0; END IF;
END;
$BODY$
LANGUAGE plpgsql VOLATILE
COST 100;
2013-08-21 15:35:46 STATUS : Refreshing function bonus_5_ma_narok...
2013-08-21 15:35:46 QUERY : Set query (****): SELECT pr.oid, pr.xmin, pr.*,
format_type(TYP.oid, NULL) AS typname, typns.nspname AS typnsp, lanname,
proargnames, pg_get_expr(proargdefaults, 'pg_catalog.pg_class'::regclass) AS
proargdefaultvals, pronargdefaults, proconfig, pg_get_userbyid(proowner)
as funcowner, description
FROM pg_proc pr
JOIN pg_type typ ON typ.oid=prorettype
JOIN pg_namespace typns ON typns.oid=typ.typnamespace
JOIN pg_language lng ON lng.oid=prolang
LEFT OUTER JOIN pg_description des ON des.objoid=pr.oid
WHERE pr.oid=471599650::oid
ORDER BY proname
2013-08-21 15:35:46 QUERY : Set query (****): SELECT oid, format_type(oid,
NULL) AS typname FROM pg_type
2013-08-21 15:35:46 INFO : Deleting Function bonus_5_ma_narok for refresh
2013-08-21 15:35:46 INFO : Replacing with new node Function bonus_5_ma_narok
for refresh
2013-08-21 15:35:46 INFO : Displaying properties for Function bonus_5_ma_narok
2013-08-21 15:35:46 STATUS : Refreshing function bonus_5_ma_narok... (0.09 secs)
--
Sent via pgadmin-support mailing list (pgadmin-support@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgadmin-support
