Received: from mail.postgresql.org (webmail.postgresql.org [216.126.85.28]) by mail.postgresql.org (8.11.1/8.11.1) with SMTP id eANHM0A74617 for ; Thu, 23 Nov 2000 12:22:00 -0500 (EST) (envelope-from pgsql-sql-owner@postgresql.org) Received: from ns.zt.ukrtel.net (ns.zt.ukrtel.net [195.5.22.130]) by mail.postgresql.org (8.11.1/8.11.1) with ESMTP id eAICHQs37285 for ; Sat, 18 Nov 2000 07:17:26 -0500 (EST) (envelope-from fonin@ziet.zhitomir.ua) Received: from machinen.localdomain (ip136.zt.ukrtel.net [195.5.22.136]) by ns.zt.ukrtel.net (8.8.8/8.8.8) with SMTP id OAA16034; Sat, 18 Nov 2000 14:15:42 +0200 Date: Sat, 18 Nov 2000 14:08:47 +0200 From: Max Fonin To: Clayton Cottingham Cc: pgsql-sql@postgresql.org Subject: Re: is there a mysql to postgresql sql converter? Message-Id: <20001118140847.45895a34.fonin@ziet.zhitomir.ua> In-Reply-To: <200011172328.eAHNSKP11431@ns.ziet.zhitomir.ua> References: <20001117231734.5ab5138c.fonin@ziet.zhitomir.ua><3A15A701.73E53846@marketingchallenge.com> <20001118011323.0637fec4.fonin@ziet.zhitomir.ua> <200011172328.eAHNSKP11431@ns.ziet.zhitomir.ua> X-Mailer: stuphead version 0.4.5 (GTK+ 1.2.6; Linux 2.2.15; i686) Mime-Version: 1.0 Content-Type: text/plain; charset=US-ASCII Content-Transfer-Encoding: 7bit X-Archive-Number: 200011/205 X-Sequence-Number: 236 Hi. > > Can give a link ? > can do : > http://freshmeat.net/projects/mysql2pgsql/?highlight=convert+sql Thank you. BTW, if you interested in this tool, I may send you fresh versions or place it on the web. Maybe you will or somebody help me with one problem ? Do you know PL/pgSQL and how to create new type in Postgres ? The problem is that I can't use new type constructed with CREATE TYPE if transform functions written on PL/pgSQL. Because PL/pgSQL doesn't support type "opaque", or I do something wrong. The same function on PL/pgSQL with input argtype "text" or "int2" works fine, but when I simply change argtype to "opaque", I get "compilation error near line 0". Source is below. Thanks, Max Rudensky. BEGIN; CREATE FUNCTION enum_access_Monday_in (opaque) RETURNS enum_access_Monday AS ' DECLARE invalue ALIAS for $1; BEGIN IF invalue='''' OR invalue=''0'' THEN RETURN 0; END IF; IF invalue=''Monday'' OR invalue=''1'' THEN RETURN 1; END IF; IF invalue=''Tuesday'' OR invalue=''2'' THEN RETURN 2; END IF; IF invalue=''Wednesday'' OR invalue=''3'' THEN RETURN 3; END IF; RAISE EXCEPTION ''incorrect input value: %'',invalue; END;' LANGUAGE 'plpgsql' WITH (ISCACHABLE); CREATE FUNCTION enum_access_Monday_out (opaque) RETURNS opaque AS ' DECLARE outvalue ALIAS for $1; BEGIN IF outvalue=0 THEN RETURN ''''; END IF; IF outvalue=1 THEN RETURN ''Monday''; END IF; IF outvalue=2 THEN RETURN ''Tuesday''; END IF; IF outvalue=3 THEN RETURN ''Wednesday''; END IF; RAISE EXCEPTION ''incorrect output value: %'',outvalue; END;' LANGUAGE 'plpgsql' WITH (ISCACHABLE); CREATE TYPE enum_access_Monday ( internallength = 2, input = enum_access_Monday_in, output = enum_access_Monday_out, PASSEDBYVALUE ); COMMIT;