Postgres Pro
Downloads
Home   >   mailing lists

Thread: CREATE TYPE with array

CREATE TYPE with array

From
Wolfgang Drotschmann
Date:
Hi folks,

I tried to create a new type with an array in it.  So I took an example
from (the german translation of) "PostgreSQL: Introduction and Concepts"
by Bruce Momjian.

This example says:

create type int4array (
       input=array_in,output=array_out,
       internallength=variable,element=int4
);


Now, psql complains:

ERROR:  TypeCreate: function array_out(int4array) does not exist

I'm using PostgreSQL 7.3.2 (came with SuSE Linux 8.1).
Have things changed, or did I overlook something?

If I ask psql

\df array

array_out (among others) appears in the list of functions.
Myfirst guess is that array_{in,out} is somehow predefined, so that I
have not to define functions by hand - or is this wrong?

Thanks!
    Wolfgang

Encrypted data.

From
Ben Clewett
Date:
Dear Novice,

I notice from section 4.28 of
http://developer.postgresql.org/docs/pgsql/doc/FAQ that encrpytion of
data within PostgreSQL is only possible by using an encrypted file
system.  I belive?

In order to protect our distributed database from it's own users, we
have a requirement to encrypt the data in the system, or by some other
means make the content unreadable.

I'm looking into finding some system for in-line coding and uncoding of
non-index fields, hopefully from clear text to same-length ascii text,
so that VARCHAR(n) can remain the same.

Firstly I was wondering whether there are any plans for this to be
included in the distribution at a low-level, so that I don't have to
code my application to do this, and therefore keys may be encrypted yet
remain correctly ordered etc.   If anybody knows, I would be interesting
in knowing, and would be interested in helping if at all possible.

Secondly, has anybody had experience with these issues, and can suggest
another acceptable way of protecting the data by making it unreadable /
unavailable to all?

Thanks for your help again,

Ben.

Re: CREATE TYPE with array

From
Tom Lane
Date:
Wolfgang Drotschmann <drotschm@fgan.de> writes:
> I tried to create a new type with an array in it.  So I took an example
> from (the german translation of) "PostgreSQL: Introduction and Concepts"
> by Bruce Momjian.

> create type int4array (
>        input=array_in,output=array_out,
>        internallength=variable,element=int4
> );
> ERROR:  TypeCreate: function array_out(int4array) does not exist

7.3 is stricter about the declarations of datatype I/O functions than
prior releases were.  Since array_out is declared to take anyarray,
not int4array, the above fails.

You could hack your way to a working datatype by creating extra pg_proc
entries for array_in/_out:

regression=# create function int4array_in(cstring) returns int4array
regression-# as 'array_in' language internal strict immutable;
NOTICE:  ProcedureCreate: type int4array is not yet defined
CREATE FUNCTION
regression=# create function int4array_out(int4array) returns cstring
regression-# as 'array_out' language internal strict immutable;
NOTICE:  Argument type "int4array" is only a shell
CREATE FUNCTION
regression=# create type int4array (
regression(# input=int4array_in, output=int4array_out,
regression(# internallength=variable,element=int4
regression(# );
CREATE TYPE
regression=#

            regards, tom lane

Re: Encrypted data.

From
Tom Lane
Date:
Ben Clewett <B.Clewett@roadrunner.uk.com> writes:
> In order to protect our distributed database from it's own users, we
> have a requirement to encrypt the data in the system, or by some other
> means make the content unreadable.

Look in the contrib/pgcrypto directory, you may find stuff that will
help.

            regards, tom lane

Re: Encrypted data.

From
Bruno Wolff III
Date:
On Tue, Jul 22, 2003 at 13:00:53 +0000,
  Ben Clewett <B.Clewett@roadrunner.uk.com> wrote:
>
> Secondly, has anybody had experience with these issues, and can suggest
> another acceptable way of protecting the data by making it unreadable /
> unavailable to all?

You might want to take a look at "Translucent Databases". It is a relatively
inexpensive paperback book that may give you some ideas on how to protect
your data while still being able to use it efficiently.

Re: CREATE TYPE with array

From
Wolfgang Drotschmann
Date:
Thank you Tom,

so far so good.  I tried the following for a database "foo" and being a
"normal" user...

Tom Lane <tgl@sss.pgh.pa.us> schrieb:
> [...]
> You could hack your way to a working datatype by creating extra pg_proc
> entries for array_in/_out:
>
> regression=# create function int4array_in(cstring) returns int4array
> regression-# as 'array_in' language internal strict immutable;
> NOTICE:  ProcedureCreate: type int4array is not yet defined
> CREATE FUNCTION
> regression=# create function int4array_out(int4array) returns cstring
> regression-# as 'array_out' language internal strict immutable;
> NOTICE:  Argument type "int4array" is only a shell
> CREATE FUNCTION
> regression=# create type int4array (
> regression(# input=int4array_in, output=int4array_out,
> regression(# internallength=variable,element=int4
> regression(# );
> CREATE TYPE
> regression=#

create function int4array_in(cstring) returns int4array
as 'array_in' language internal strict immutable;
ERROR:  internal: permission denied

Okay, switch to "root":

\c foo postgres
Password:
You are now connected to database foo as user postgres.
create function int4array_in(cstring) returns int4array
as 'array_in' language internal strict immutable;
NOTICE:  ProcedureCreate: type int4array is not yet defined
CREATE FUNCTION

create function int4array_out(int4array) returns cstring
as 'array_out' language internal strict immutable;
NOTICE:  Argument type "int4array" is only a shell
CREATE FUNCTION

Back to user "bpe" and trying to create a type

create type int4array (
input=int4array_in, output=int4array_out,
internallength=variable,element=int4
);
CREATE TYPE

Now!  Creating a table...

create table xs ( a char(5), l int4array[]);
CREATE TABLE

...and inserting a row:

insert into xs values('abcde', '{1,2,3,4}');
ERROR:  array_in: Need to specify dimension

Ups!?!

Best regards,
    Wolfgang

Re: CREATE TYPE with array

From
Tom Lane
Date:
Wolfgang Drotschmann <drotschm@fgan.de> writes:
> create table xs ( a char(5), l int4array[]);
> CREATE TABLE
> insert into xs values('abcde', '{1,2,3,4}');
> ERROR:  array_in: Need to specify dimension

int4array is already an array; I don't think you want to make an array
of arrays.  It might work if you wrote the value as something like

    '{''{1,2,3}'',''{4,5,6}''}'

(not sure that I have the quoting quite right here) but that sure seems
like the hard way compared to using a multidimensional array.

In short, declare l as int4array not int4array[].

            regards, tom lane

Re: CREATE TYPE with array

From
Wolfgang Drotschmann
Date:
Hi Tom!

Tom Lane schrieb:
> [...]
> In short, declare l as int4array not int4array[].

You're right - I must have been blind.

Thanks a lot!

Best regards,
    Wolfgang

Re: CREATE TYPE with array

From
"Sanjeev Sharma (Kinera)"
Date:
HI ALL,
             I'am a new comer to postgres and I'am facing a problem.Please
help me out. How do we convert Varchar field to Numeric , I have used
CAST( Var As Numeric) but it is giving error and even to_number is not
working . Any help will be highly appreciated.
Thanks
Sanjeev

----- Original Message -----
From: "Wolfgang Drotschmann" <drotschm@fgan.de>
To: "Tom Lane" <tgl@sss.pgh.pa.us>
Cc: <pgsql-novice@postgresql.org>
Sent: Thursday, July 24, 2003 11:27 AM
Subject: Re: [NOVICE] CREATE TYPE with array


> Hi Tom!
>
> Tom Lane schrieb:
> > [...]
> > In short, declare l as int4array not int4array[].
>
> You're right - I must have been blind.
>
> Thanks a lot!
>
> Best regards,
> Wolfgang
>
> ---------------------------(end of broadcast)---------------------------
> TIP 5: Have you checked our extensive FAQ?
>
>                http://www.postgresql.org/docs/faqs/FAQ.html