Postgres Pro
Downloads
Home   >   mailing lists

Thread: PK referenced function

PK referenced function

From
Agustin Larreinegabe
Date:
Hi everyone:

I want to know if exists a postgres function or some easy way to know if a PK in a table is already referenced in another table/tables.

e.g.
I want to delete a row but first I've got to change or delete in the table/tables where is referenced, and I have many table where could be referenced. 

Re: PK referenced function

From
Serge Fonville
Date:
Hi Augustin,

PostgreSQL has a couple of possibilities in this regard.
For example, http://stackoverflow.com/questions/1152260/postgres-sql-to-list-table-foreign-keys has a couple of very clear queries.

Additionally, it would be very useful if you could further clarify the problem you are trying to solve.

HTH


Kind regards/met vriendelijke groet,

Serge Fonville

http://www.sergefonville.nl

Convince Microsoft!


2013/9/6 Agustin Larreinegabe <alarreine@gmail.com>
Hi everyone:

I want to know if exists a postgres function or some easy way to know if a PK in a table is already referenced in another table/tables.

e.g.
I want to delete a row but first I've got to change or delete in the table/tables where is referenced, and I have many table where could be referenced. 

Re: PK referenced function

From
Michael Paquier
Date:
On Fri, Sep 6, 2013 at 10:26 PM, Agustin Larreinegabe
<alarreine@gmail.com> wrote:
> I want to know if exists a postgres function or some easy way to know if a
> PK in a table is already referenced in another table/tables.
psql has all you want for that. For example in this case:
=# create table aa (a int primary key);
CREATE TABLE
=# create table bb (a int references aa);
CREATE TABLE
=# create table cc (a int references aa);
CREATE TABLE
=# \d aa
      Table "public.aa"
 Column |  Type   | Modifiers
--------+---------+-----------
 a      | integer | not null
Indexes:
    "aa_pkey" PRIMARY KEY, btree (a)
Referenced by:
    TABLE "bb" CONSTRAINT "bb_a_fkey" FOREIGN KEY (a) REFERENCES aa(a)
    TABLE "cc" CONSTRAINT "cc_a_fkey" FOREIGN KEY (a) REFERENCES aa(a)
Running a simple ¥d on the relation having the primary key also lists
where is is referenced...

Now by using psql -E you can output as well the queries used by psql
to fetch this information from server, and in your case here is how to
get the foreign keys referencing it:
SELECT conname, conrelid::pg_catalog.regclass,
  pg_catalog.pg_get_constraintdef(c.oid, true) as condef
FROM pg_catalog.pg_constraint c
WHERE c.confrelid = '$RELATION_NAME'::regclass AND c.contype = 'f' ORDER BY 1;
Simply replace RELATION_NAME by what you want.

> e.g.
> I want to delete a row but first I've got to change or delete in the
> table/tables where is referenced, and I have many table where could be
> referenced.
Do that with ON DELETE/UPDATE CASCADE when defining a foreign key.
Here is an example with ON DELETE CASCADE:
=# create table aa (a int primary key);
CREATE TABLE
=# create table dd (a int references aa on delete cascade);
CREATE TABLE
=# insert into aa values (1);
INSERT 0 1
=# insert into dd values (1);
INSERT 0 1
=# delete from aa where a = 1;
DELETE 1
=# select * from dd;
 a
---
(0 rows)
Documentation is here for reference:
http://www.postgresql.org/docs/9.2/static/ddl-constraints.html.
--
Michael

Re: PK referenced function

From
Agustin Larreinegabe
Date:
Thanks a lot


On Fri, Sep 6, 2013 at 9:51 AM, Michael Paquier <michael.paquier@gmail.com> wrote:
On Fri, Sep 6, 2013 at 10:26 PM, Agustin Larreinegabe
<alarreine@gmail.com> wrote:
> I want to know if exists a postgres function or some easy way to know if a
> PK in a table is already referenced in another table/tables.
psql has all you want for that. For example in this case:
=# create table aa (a int primary key);
CREATE TABLE
=# create table bb (a int references aa);
CREATE TABLE
=# create table cc (a int references aa);
CREATE TABLE
=# \d aa
      Table "public.aa"
 Column |  Type   | Modifiers
--------+---------+-----------
 a      | integer | not null
Indexes:
    "aa_pkey" PRIMARY KEY, btree (a)
Referenced by:
    TABLE "bb" CONSTRAINT "bb_a_fkey" FOREIGN KEY (a) REFERENCES aa(a)
    TABLE "cc" CONSTRAINT "cc_a_fkey" FOREIGN KEY (a) REFERENCES aa(a)
Running a simple ¥d on the relation having the primary key also lists
where is is referenced...

Now by using psql -E you can output as well the queries used by psql
to fetch this information from server, and in your case here is how to
get the foreign keys referencing it:
SELECT conname, conrelid::pg_catalog.regclass,
  pg_catalog.pg_get_constraintdef(c.oid, true) as condef
FROM pg_catalog.pg_constraint c
WHERE c.confrelid = '$RELATION_NAME'::regclass AND c.contype = 'f' ORDER BY 1;
Simply replace RELATION_NAME by what you want.

> e.g.
> I want to delete a row but first I've got to change or delete in the
> table/tables where is referenced, and I have many table where could be
> referenced.
Do that with ON DELETE/UPDATE CASCADE when defining a foreign key.
Here is an example with ON DELETE CASCADE:
=# create table aa (a int primary key);
CREATE TABLE
=# create table dd (a int references aa on delete cascade);
CREATE TABLE
=# insert into aa values (1);
INSERT 0 1
=# insert into dd values (1);
INSERT 0 1
=# delete from aa where a = 1;
DELETE 1
=# select * from dd;
 a
---
(0 rows)
Documentation is here for reference:
http://www.postgresql.org/docs/9.2/static/ddl-constraints.html.
--
Michael



--
Gracias
-----------------
Agustín Larreinegabe

Re: PK referenced function

From
Agustin Larreinegabe
Date:
This is what I did with your help,
So with this function you can know if a PK in table_from is referenced in x table with CONSTRAINT FOREIGN KEY

Just if someone needs

CREATE OR REPLACE FUNCTION referenced_in (
  in_id bigint,
  in_schema_from varchar,
  in_table_from varchar
)
RETURNS TABLE (
  is_referenced_in_table varchar,
  in_row_with_pk bigint
) AS
$body$
DECLARE
v_foreign_tables record;
sql varchar;
BEGIN

FOR v_foreign_tables IN SELECT distinct
    tc.table_schema,tc.table_name, kcu.column_name,
    ccu.table_schema foreign_table_schema,ccu.table_name AS foreign_table_name,
    ccu.column_name AS foreign_column_name
FROM
    information_schema.table_constraints AS tc
    JOIN information_schema.key_column_usage 
        AS kcu ON tc.constraint_name = kcu.constraint_name
    JOIN information_schema.constraint_column_usage 
        AS ccu ON ccu.constraint_name = tc.constraint_name
WHERE constraint_type = 'FOREIGN KEY' and ccu.table_name=in_table_from and ccu.table_schema=in_schema_from
LOOP

sql='Select '||quote_literal(v_foreign_tables.table_schema||'.'||v_foreign_tables.table_name)||'::varchar,id from '||v_foreign_tables.table_schema||'.'||v_foreign_tables.table_name||' where '||v_foreign_tables.column_name||'='||in_id;

RETURN QUERY EXECUTE sql;
 

END LOOP;
END;
$body$
LANGUAGE 'plpgsql'
VOLATILE
CALLED ON NULL INPUT
SECURITY INVOKER
COST 100 ROWS 1000;


On Fri, Sep 6, 2013 at 10:17 AM, Agustin Larreinegabe <alarreine@gmail.com> wrote:
Thanks a lot


On Fri, Sep 6, 2013 at 9:51 AM, Michael Paquier <michael.paquier@gmail.com> wrote:
On Fri, Sep 6, 2013 at 10:26 PM, Agustin Larreinegabe
<alarreine@gmail.com> wrote:
> I want to know if exists a postgres function or some easy way to know if a
> PK in a table is already referenced in another table/tables.
psql has all you want for that. For example in this case:
=# create table aa (a int primary key);
CREATE TABLE
=# create table bb (a int references aa);
CREATE TABLE
=# create table cc (a int references aa);
CREATE TABLE
=# \d aa
      Table "public.aa"
 Column |  Type   | Modifiers
--------+---------+-----------
 a      | integer | not null
Indexes:
    "aa_pkey" PRIMARY KEY, btree (a)
Referenced by:
    TABLE "bb" CONSTRAINT "bb_a_fkey" FOREIGN KEY (a) REFERENCES aa(a)
    TABLE "cc" CONSTRAINT "cc_a_fkey" FOREIGN KEY (a) REFERENCES aa(a)
Running a simple ¥d on the relation having the primary key also lists
where is is referenced...

Now by using psql -E you can output as well the queries used by psql
to fetch this information from server, and in your case here is how to
get the foreign keys referencing it:
SELECT conname, conrelid::pg_catalog.regclass,
  pg_catalog.pg_get_constraintdef(c.oid, true) as condef
FROM pg_catalog.pg_constraint c
WHERE c.confrelid = '$RELATION_NAME'::regclass AND c.contype = 'f' ORDER BY 1;
Simply replace RELATION_NAME by what you want.

> e.g.
> I want to delete a row but first I've got to change or delete in the
> table/tables where is referenced, and I have many table where could be
> referenced.
Do that with ON DELETE/UPDATE CASCADE when defining a foreign key.
Here is an example with ON DELETE CASCADE:
=# create table aa (a int primary key);
CREATE TABLE
=# create table dd (a int references aa on delete cascade);
CREATE TABLE
=# insert into aa values (1);
INSERT 0 1
=# insert into dd values (1);
INSERT 0 1
=# delete from aa where a = 1;
DELETE 1
=# select * from dd;
 a
---
(0 rows)
Documentation is here for reference:
http://www.postgresql.org/docs/9.2/static/ddl-constraints.html.
--
Michael



--
Gracias
-----------------
Agustín Larreinegabe



--
Gracias
-----------------
Agustín Larreinegabe