Re: PK referenced function

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(at)gmail(dot)com>wrote:

> Thanks a lot
>
>
> On Fri, Sep 6, 2013 at 9:51 AM, Michael Paquier <michael(dot)paquier(at)gmail(dot)com
> > wrote:
>
>> On Fri, Sep 6, 2013 at 10:26 PM, Agustin Larreinegabe
>> <alarreine(at)gmail(dot)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