<!-- $Header: /cvsroot/pgsql/doc/src/sgml/ref/create_conversion.sgml,v 1.4 2002/09/21 18:32:54 petere Exp $ -->

<!-- $Header: /cvsroot/pgsql/doc/src/sgml/ref/create_conversion.sgml,v 1.5 2002/11/02 02:33:03 tgl Exp $ -->

<refentry id="SQL-CREATECONVERSION">

<refmeta>

<refsynopsisdiv>

<synopsis>

CREATE [DEFAULT] CONVERSION <replaceable>conversion_name</replaceable>

FOR <replaceable>source_encoding</replaceable>

TO <replaceable>dest_encoding</replaceable> FROM <replaceable>funcname</replaceable>

FOR <replaceable>source_encoding</replaceable> TO <replaceable>dest_encoding</replaceable> FROM <replaceable>funcname</replaceable>

</synopsis>

</refsynopsisdiv>

<para>

<command>CREATE CONVERSION</command> defines a new encoding

conversion. There are two kinds of conversions. A default

conversion is used for an automatic encoding conversion between

frontend and backend. There should be only one default conversion

for source/destination encodings pair in a schema. None default

conversion never be used for the automatic conversion. Instead it

can be used for CONVERT() function.

conversion. Conversion names may be used in the CONVERT() function

to specify a particular encoding conversion. Also, conversions that

are marked DEFAULT can be used for automatic encoding conversion between

frontend and backend. For this purpose, two conversions, from encoding A to

B AND from encoding B to A, must be defined.

</para>

<para>

To be able to create a conversion, you must have the execute right

on the function and the usage right on the schema the function

belongs to.

on the function and the create right on the destination schema.

</para>

<variablelist>

The <literal>DEFAULT</> clause indicates that this conversion

is the default for this particular source to destination

encoding. There should be only one default encoding in a schema

for the encoding pair. A default encoding can be used for not

only CONVERT() function, but also for the automatic encoding

conversion between frontend and backend. For this purpose, two

conversions, from encoding A to B AND encoding B to A, must be

defined.

for the encoding pair.

</para>

</listitem>

</varlistentry>

<listitem>

<para>

The name of the conversion. The conversion name may be

schema-qualified. If it is not,a conversion is defined in the

current schema. The conversion name must be uniquewith in a

schema-qualified. If it is not,the conversion is defined in the

current schema. The conversion name must be uniquewithin a

schema.

</para>

</listitem>

</para>

<para>

The function must have following signature:

The function must havethefollowing signature:

<programlisting>

conv_proc(

FindConversionByName(List*name)

{

char*schemaname;

char*conversion_name;

OidnamespaceId;

Oidconoid;

List*lptr;

namespaceId=QualifiedNameGetCreationNamespace(name,&conversion_name);

DeconstructQualifiedName(name,&schemaname,&conversion_name);

if (length(name)>1)

if (schemaname)

{

if (pg_namespace_aclcheck(namespaceId,GetUserId(),ACL_USAGE)!=ACLCHECK_OK)

returnInvalidOid;

namespaceId=LookupExplicitNamespace(schemaname);

returnFindConversion(conversion_name,namespaceId);

}

recomputeNamespacePath();

foreach(lptr,namespaceSearchPath)

{

OidnamespaceId= (Oid)lfirsti(lptr);

recomputeNamespacePath();

conoid=FindConversion(conversion_name,namespaceId);

if (OidIsValid(conoid))

returnconoid;

foreach(lptr,namespaceSearchPath)

{

namespaceId= (Oid)lfirsti(lptr);

conoid=FindConversion(conversion_name,namespaceId);

if (OidIsValid(conoid))

returnconoid;

}

}

returnInvalidOid;

}

FindDefaultConversionProc(int4for_encoding,int4to_encoding)

ConversionCreate(constchar*conname,Oidconnamespace,

int32conowner,

int4conforencoding,int4contoencoding,

int32conforencoding,int32contoencoding,

Oidconproc,booldef)

{

inti;

elog(ERROR,"no conversion name supplied");

if (SearchSysCacheExists(CONNAMESP,

if (SearchSysCacheExists(CONNAMENSP,

PointerGetDatum(conname),

ObjectIdGetDatum(connamespace),

0,0))

conforencoding,

contoencoding))

elog(ERROR,"default conversion for %s to %s already exists",

pg_encoding_to_char(conforencoding),pg_encoding_to_char(contoencoding));

pg_encoding_to_char(conforencoding),

pg_encoding_to_char(contoencoding));

}

values[Anum_pg_conversion_conforencoding-1]=Int32GetDatum(conforencoding);

values[Anum_pg_conversion_contoencoding-1]=Int32GetDatum(contoencoding);

values[Anum_pg_conversion_conproc-1]=ObjectIdGetDatum(conproc);

if (def== true)

values[Anum_pg_conversion_condefault-1]=BoolGetDatum(def);

nulls[Anum_pg_conversion_condefault-1]='n';

values[Anum_pg_conversion_condefault-1]=BoolGetDatum(def);

tup=heap_formtuple(tupDesc,values,nulls);

CatalogUpdateIndexes(rel,tup);

myself.classId=get_system_catalog_relid(ConversionRelationName);

myself.classId=RelationGetRelid(rel);

myself.objectId=HeapTupleGetOid(tup);

myself.objectSubId=0;

referenced.classId=RelOid_pg_proc;

referenced.objectId=conproc;

referenced.objectSubId=0;

returnoid;

}

ConversionDrop(constchar*conname,Oidconnamespace,

int32conowner,DropBehaviorbehavior)

ConversionDrop(OidconversionOid,DropBehaviorbehavior)

{

Relationrel;

TupleDesctupDesc;

HeapTupletuple;

HeapScanDescscan;

ScanKeyDatascanKeyData;

Form_pg_conversionbody;

ObjectAddressobject;

Oidmyoid;

if (!conname)

elog(ERROR,"no conversion name supplied");

ScanKeyEntryInitialize(&scanKeyData,

0,

Anum_pg_conversion_connamespace,

F_OIDEQ,

ObjectIdGetDatum(connamespace));

rel=heap_openr(ConversionRelationName,AccessShareLock);

tupDesc=rel->rd_att;

scan=heap_beginscan(rel,SnapshotNow,

1,&scanKeyData);

while (HeapTupleIsValid(tuple=heap_getnext(scan,ForwardScanDirection)))

{

body= (Form_pg_conversion)GETSTRUCT(tuple);

if (!strncmp(NameStr(body->conname),conname,NAMEDATALEN))

break;

}

tuple=SearchSysCache(CONOID,

ObjectIdGetDatum(conversionOid),

0,0,0);

if (!HeapTupleIsValid(tuple))

{

elog(ERROR,"conversion %s not found",conname);

return;

}

elog(ERROR,"Conversion %u search from syscache failed",

conversionOid);

if (!superuser()&& ((Form_pg_conversion)GETSTRUCT(tuple))->conowner!=GetUserId())

if (!superuser()&&

((Form_pg_conversion)GETSTRUCT(tuple))->conowner!=GetUserId())

elog(ERROR,"permission denied");

myoid=HeapTupleGetOid(tuple);

heap_endscan(scan);

heap_close(rel,AccessShareLock);

ReleaseSysCache(tuple);

object.classId=get_system_catalog_relid(ConversionRelationName);

object.objectId=myoid;

object.objectId=conversionOid;

object.objectSubId=0;

performDeletion(&object,behavior);

}

RemoveConversionById(OidconversionOid)

{

heap_close(rel,RowExclusiveLock);

}

FindDefaultConversion(Oidname_space,int4for_encoding,int4to_encoding)

FindDefaultConversion(Oidname_space,int32for_encoding,int32to_encoding)

{

CatCList*catlist;

HeapTupletuple;

Form_pg_conversionbody;

Oidproc=InvalidOid;

inti;

if (pg_namespace_aclcheck(name_space,GetUserId(),ACL_USAGE)!=ACLCHECK_OK)

returnproc;

catlist=SearchSysCacheList(CONDEFAULT,3,

ObjectIdGetDatum(name_space),

Int32GetDatum(for_encoding),

{

tuple=&catlist->members[i]->tuple;

body= (Form_pg_conversion)GETSTRUCT(tuple);

if (body->condefault== TRUE)

if (body->condefault)

{

proc=body->conproc;

break;

returnproc;

}

FindConversion(constchar*conname,Oidconnamespace)

AclResultaclresult;

tuple=SearchSysCache(CONNAMESP,

tuple=SearchSysCache(CONNAMENSP,

PointerGetDatum(conname),

ObjectIdGetDatum(connamespace),

0,0);

if (!HeapTupleIsValid(tuple))

returnInvalidOid;

procoid= ((Form_pg_conversion)GETSTRUCT(tuple))->conproc;

conoid=HeapTupleGetOid(tuple);