Re: arrays as pgsql function parameters

On Fri, 7 Jun 2002, Joseph Syjuco wrote:

Have a look at the documentation regarding server side programming.

Also you can have as examples the contrib/array and the famous
contrib/intarray package.

> how can i pass and access array parameters to a function
>
> ---------------------------(end of broadcast)---------------------------
> TIP 2: you can get off all lists at once with the unregister command
> (send "unregister YourEmailAddressHere" to majordomo(at)postgresql(dot)org)
>

--
Achilleus Mantzios
S/W Engineer
IT dept
Dynacom Tankers Mngmt
tel: +30-10-8981112
fax: +30-10-8981877
email: achill(at)matrix(dot)gatewaynet(dot)com
mantzios(at)softlab(dot)ece(dot)ntua(dot)gr

On Fri, 7 Jun 2002, Joseph Syjuco wrote:

> actually i already have and basically the function statments start as
> create function test(_int4) returns integer
> when i tried to execute the function
> select test('{1,2,3,4}');
> it didnt spew out errors but it didnt output results either

Well, it depends on the code :)
Is it C? pg/plsql??

>
> -----Original Message-----
> From: pgsql-sql-owner(at)postgresql(dot)org
> [mailto:pgsql-sql-owner(at)postgresql(dot)org]On Behalf Of Achilleus Mantzios
> Sent: Friday, June 07, 2002 7:24 PM
> To: Joseph Syjuco
> Cc: pgsql-sql(at)postgresql(dot)org
> Subject: Re: [SQL] arrays as pgsql function parameters
>
>
> On Fri, 7 Jun 2002, Joseph Syjuco wrote:
>
> Have a look at the documentation regarding server side programming.
>
> Also you can have as examples the contrib/array and the famous
> contrib/intarray package.
>
> > how can i pass and access array parameters to a function
> >
> > ---------------------------(end of broadcast)---------------------------
> > TIP 2: you can get off all lists at once with the unregister command
> > (send "unregister YourEmailAddressHere" to majordomo(at)postgresql(dot)org)
> >
>
>

--
Achilleus Mantzios
S/W Engineer
IT dept
Dynacom Tankers Mngmt
tel: +30-10-8981112
fax: +30-10-8981877
email: achill(at)matrix(dot)gatewaynet(dot)com
mantzios(at)softlab(dot)ece(dot)ntua(dot)gr

actually i already have and basically the function statments start as
create function test(_int4) returns integer
when i tried to execute the function
select test('{1,2,3,4}');
it didnt spew out errors but it didnt output results either

-----Original Message-----
From: pgsql-sql-owner(at)postgresql(dot)org
[mailto:pgsql-sql-owner(at)postgresql(dot)org]On Behalf Of Achilleus Mantzios
Sent: Friday, June 07, 2002 7:24 PM
To: Joseph Syjuco
Cc: pgsql-sql(at)postgresql(dot)org
Subject: Re: [SQL] arrays as pgsql function parameters

On Fri, 7 Jun 2002, Joseph Syjuco wrote:

Have a look at the documentation regarding server side programming.

Also you can have as examples the contrib/array and the famous
contrib/intarray package.

> how can i pass and access array parameters to a function
>
> ---------------------------(end of broadcast)---------------------------
> TIP 2: you can get off all lists at once with the unregister command
> (send "unregister YourEmailAddressHere" to majordomo(at)postgresql(dot)org)
>

--
Achilleus Mantzios
S/W Engineer
IT dept
Dynacom Tankers Mngmt
tel: +30-10-8981112
fax: +30-10-8981877
email: achill(at)matrix(dot)gatewaynet(dot)com
mantzios(at)softlab(dot)ece(dot)ntua(dot)gr

---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org

Joseph,

> actually i already have and basically the function statments start as
> create function test(_int4) returns integer
> when i tried to execute the function
> select test('{1,2,3,4}');
> it didnt spew out errors but it didnt output results either

How about posting a full function definition?

We're not telepathic.

-Josh

how can i pass and access array parameters to a function

can someone have a foreign key constraint that references
a view??

--
Achilleus Mantzios
S/W Engineer
IT dept
Dynacom Tankers Mngmt
tel: +30-10-8981112
fax: +30-10-8981877
email: achill(at)matrix(dot)gatewaynet(dot)com
mantzios(at)softlab(dot)ece(dot)ntua(dot)gr

Josh Berkus wrote:
>
> Joseph,
>
> > actually i already have and basically the function statments start as
> > create function test(_int4) returns integer
> > when i tried to execute the function
> > select test('{1,2,3,4}');
> > it didnt spew out errors but it didnt output results either
>
> How about posting a full function definition?
>
> We're not telepathic.
>
> -Josh

here is the sample function that i got from the internet.
hope you can send me a sample function

create function foo(_int4) returns int2 as'
declare
a _int4 alias for $1;
i int:=1;
begin
while a[i] loop
i:=i+1;
end loop;
return i-1;
end;
' language 'plpgsql';

On Mon, 10 Jun 2002, Achilleus Mantzios wrote:

>
> can someone have a foreign key constraint that references
> a view??

Not currently in PostgreSQL. Doing so for a general case
gets rather involved.

Achilleus Mantzios wrote:
>
> can someone have a foreign key constraint that references
> a view??

No, and this is not planned either. Remember that it is not
only required for referential integrity to check if a key
exists on INSERT or UPDATE to the referencing table. The
system must guarantee that you cannot remove existing keys
while they are referenced (or more precise perform the
requested referential action).

Jan

--

#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me. #
#================================================== JanWieck(at)Yahoo(dot)com #

I would just like to elaborate, and clarify if my understanding is correct:

The implication of below is that you need a trigger in the foreign key
target table on the DELETE event, so the foreign key table only lets you
delete a row if there are no other tables refering to the key you want to
delete.

Views cannot have triggers, hence cannot have a DELETE trigger, therefore
that is why the view cannot be a foreign key target table.

Terry Fielder
Network Engineer
Great Gulf Homes / Ashton Woods Homes
terry(at)greatgulfhomes(dot)com

> -----Original Message-----
> From: pgsql-general-owner(at)postgresql(dot)org
> [mailto:pgsql-general-owner(at)postgresql(dot)org]On Behalf Of Jan Wieck
> Sent: Monday, June 10, 2002 1:20 PM
> To: Achilleus Mantzios
> Cc: pgsql-general(at)postgresql(dot)org; pgsql-sql(at)postgresql(dot)org
> Subject: Re: [GENERAL] [SQL] VIEWs and FOREIGN keys
>
>
> Achilleus Mantzios wrote:
> >
> > can someone have a foreign key constraint that references
> > a view??
>
> No, and this is not planned either. Remember that it is not
> only required for referential integrity to check if a key
> exists on INSERT or UPDATE to the referencing table. The
> system must guarantee that you cannot remove existing keys
> while they are referenced (or more precise perform the
> requested referential action).
>
>
> Jan
>
> --
>
> #=============================================================
> =========#
> # It's easier to get forgiveness for being wrong than for
> being right. #
> # Let's break this rule - forgive me.
> #
> #==================================================
> JanWieck(at)Yahoo(dot)com #
>
>
>
> ---------------------------(end of
> broadcast)---------------------------
> TIP 3: if posting/reading through Usenet, please send an appropriate
> subscribe-nomail command to majordomo(at)postgresql(dot)org so that your
> message can get through to the mailing list cleanly
>

terry(at)greatgulfhomes(dot)com wrote:
> I would just like to elaborate, and clarify if my understanding is correct:
>
> The implication of below is that you need a trigger in the foreign key
> target table on the DELETE event, so the foreign key table only lets you
> delete a row if there are no other tables refering to the key you want to
> delete.
>
> Views cannot have triggers, hence cannot have a DELETE trigger, therefore
> that is why the view cannot be a foreign key target table.

Right, the primary key table (what you refer to as the
foreign key target) needs to have a trigger on DELETE and
UPDATE (the key value could change and break referential
integrity by doing so). For simple views this might be
doable with a trigger on the base tables, but imagine this:

CREATE VIEW pk_view AS
SELECT t1.keypart1 || t2.keypart2 AS primkey
FROM t1, t2 WHERE t1.isactive;

CREATE TABLE fk_table (
ref varchar,
FOREIGN KEY (ref) REFERENCES pk_view (primkey)
);

Okay, let's ignore the fact that the ANSI SQL spec requires
every referenced key to have a UNIQUE constraint, and that we
cannot guarantee that in the first place.

We toggle t1.isactive on a row to false, thereby removing a
few thousand result rows from the view's result set. Any cool
idea how to check if that doesn't produce some orphaned rows
in "fk_table"? By "cool idea" I mean not a couple hand
crafted PL/pgSQL triggers, but some general solution that
works with any view.

Jan

> [...]
> > -----Original Message-----
> > From: pgsql-general-owner(at)postgresql(dot)org
> >
> > Achilleus Mantzios wrote:
> > >
> > > can someone have a foreign key constraint that references
> > > a view??
> >
> > No, and this is not planned either. Remember that it is not
> > only required for referential integrity to check if a key
> > exists on INSERT or UPDATE to the referencing table. The
> > system must guarantee that you cannot remove existing keys
> > while they are referenced (or more precise perform the
> > requested referential action).

--

#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me. #
#================================================== JanWieck(at)Yahoo(dot)com #

Right, I had forgotten that the foreign key is supposed, to be unique.

Not withstanding that, a general solution would have to handle more then
just updates via the query, it would also have to handle updates done
directly to (one or more) of the views source table.

Therefore, ultimately you will need a trigger in ALL the source tables for
DELETE/UPDATE events, and they would need to know that logic of the VIEW's
WHERE clause and how it restricts result rows, therefore it could then
determine if the change to the underlying table would break the FK
constraint on the view.

Having said that, I am sure I could write pseudocode to do this.

However, given the fact that:
a) the view can be very complex
b) the update/delete query to the underlying can be extremely complex (eg
nested selects, updates, IN constraints, etc)

The coded solution would get very ugly, very fast.

This clears things up for me, I wish I had a "cool idea" to implement this
functionality, but I do not. Weighed against the complexity of implementing
it, the benefit acquired is not worth the brain damage to get there.

If the user *really* needed an FK to a view, then how about setting up a
table that contains the info of the view, updated by triggers hanging off
the source tables for the view that copy the result of the view into the the
views mirror table. This is not an efficient solution, and not even viable
if the view produces a large dataset, but just a thought for the users that
really *really* want to "foreign key into a view" ...

Terry Fielder
Network Engineer
Great Gulf Homes / Ashton Woods Homes
terry(at)greatgulfhomes(dot)com

> -----Original Message-----
> From: Jan Wieck [mailto:janwieck(at)yahoo(dot)com]
> Sent: Monday, June 10, 2002 3:20 PM
> To: terry(at)greatgulfhomes(dot)com
> Cc: 'Jan Wieck'; 'Achilleus Mantzios'; pgsql-general(at)postgresql(dot)org;
> pgsql-sql(at)postgresql(dot)org
> Subject: Re: [GENERAL] [SQL] VIEWs and FOREIGN keys
>
>
> terry(at)greatgulfhomes(dot)com wrote:
> > I would just like to elaborate, and clarify if my
> understanding is correct:
> >
> > The implication of below is that you need a trigger in the
> foreign key
> > target table on the DELETE event, so the foreign key table
> only lets you
> > delete a row if there are no other tables refering to the
> key you want to
> > delete.
> >
> > Views cannot have triggers, hence cannot have a DELETE
> trigger, therefore
> > that is why the view cannot be a foreign key target table.
>
> Right, the primary key table (what you refer to as the
> foreign key target) needs to have a trigger on DELETE and
> UPDATE (the key value could change and break referential
> integrity by doing so). For simple views this might be
> doable with a trigger on the base tables, but imagine this:
>
> CREATE VIEW pk_view AS
> SELECT t1.keypart1 || t2.keypart2 AS primkey
> FROM t1, t2 WHERE t1.isactive;
>
> CREATE TABLE fk_table (
> ref varchar,
> FOREIGN KEY (ref) REFERENCES pk_view (primkey)
> );
>
> Okay, let's ignore the fact that the ANSI SQL spec requires
> every referenced key to have a UNIQUE constraint, and that we
> cannot guarantee that in the first place.
>
> We toggle t1.isactive on a row to false, thereby removing a
> few thousand result rows from the view's result set. Any cool
> idea how to check if that doesn't produce some orphaned rows
> in "fk_table"? By "cool idea" I mean not a couple hand
> crafted PL/pgSQL triggers, but some general solution that
> works with any view.
>
>
> Jan
>
> > [...]
> > > -----Original Message-----
> > > From: pgsql-general-owner(at)postgresql(dot)org
> > >
> > > Achilleus Mantzios wrote:
> > > >
> > > > can someone have a foreign key constraint that references
> > > > a view??
> > >
> > > No, and this is not planned either. Remember that it is not
> > > only required for referential integrity to check if a key
> > > exists on INSERT or UPDATE to the referencing table. The
> > > system must guarantee that you cannot remove existing keys
> > > while they are referenced (or more precise perform the
> > > requested referential action).
>
> --
>
> #=============================================================
> =========#
> # It's easier to get forgiveness for being wrong than for
> being right. #
> # Let's break this rule - forgive me.
> #
> #==================================================
> JanWieck(at)Yahoo(dot)com #
>

terry(at)greatgulfhomes(dot)com writes:
> I would just like to elaborate, and clarify if my understanding is correct:
> The implication of below is that you need a trigger in the foreign key
> target table on the DELETE event, so the foreign key table only lets you
> delete a row if there are no other tables refering to the key you want to
> delete.
> Views cannot have triggers, hence cannot have a DELETE trigger, therefore
> that is why the view cannot be a foreign key target table.

Got it in one.

One could perhaps imagine putting triggers on the table(s) that are
referenced by the view, but those triggers would have to do very slow
and expensive things to enforce the deletion constraint, at least in the
interesting cases where the view is a join, aggregation, GROUP BY, etc...

regards, tom lane

ok thnx to all.
I did the trick with a custom trigger.

--
Achilleus Mantzios
S/W Engineer
IT dept
Dynacom Tankers Mngmt
tel: +30-10-8981112
fax: +30-10-8981877
email: achill(at)matrix(dot)gatewaynet(dot)com
mantzios(at)softlab(dot)ece(dot)ntua(dot)gr