Re: PK referenced function - Mailing list pgsql-general
| From | Agustin Larreinegabe |
|---|---|
| Subject | Re: PK referenced function |
| Date | |
| Msg-id | CALQFU685aKEhwB9E3Ajc3yHRAZJsu=TEH98zNqpGUV2XjXn4+g@mail.gmail.com Whole thread Raw |
| In response to | Re: PK referenced function (Agustin Larreinegabe <alarreine@gmail.com>) |
| List | pgsql-general |
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 Larreinegabepsql has all you want for that. For example in this case:
<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.
=# 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.Do that with ON DELETE/UPDATE CASCADE when defining a foreign key.
> 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.
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
pgsql-general by date: