PK referenced function

Hi Augustin,

PostgreSQL has a couple of possibilities in this regard.
For example,
http://stackoverflow.com/questions/1152260/postgres-sql-to-list-table-foreign-keyshas
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!
Please don't get rid of the MCM and MCA
programs<https://connect.microsoft.com/SQLServer/feedback/details/417926/truncate-partition-of-partitioned-table&gt;

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

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

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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

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