pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Max Fonin <fonin@ziet.zhitomir.ua>
To: Clayton Cottingham <clayton@marketingchallenge.com>
Cc: pgsql-sql@postgresql.org
Subject: Re: is there a mysql to postgresql sql converter?
Date: Sat, 18 Nov 2000 14:08:47 +0200
Message-ID: <20001118140847.45895a34.fonin@ziet.zhitomir.ua> (raw)
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>

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;



view thread (8+ messages)  latest in thread

Message-ID: <20001118140847.45895a34.fonin@ziet.zhitomir.ua>
Permalink:  ../20001118140847.45895a34.fonin@ziet.zhitomir.ua/
Also on:    postgresql.org/message-id/20001118140847.45895a34.fonin@ziet.zhitomir.ua

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients from your mail client; the link fills in
  the headers below: reply via email
  Some clients, webmail especially, drop In-Reply-To and References from
  a link, and the reply then starts a new thread: use the mbox below.

  To: pgsql-sql@postgresql.org
  Cc: fonin@ziet.zhitomir.ua, clayton@marketingchallenge.com
  Subject: Re: is there a mysql to postgresql sql converter?
  In-Reply-To: <20001118140847.45895a34.fonin@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> <20001118140847.45895a34.fonin@ziet.zhitomir.ua>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox