Forums PostgreSQL.fr

Michel Depiesse

Membre

Comment écrire dans un fichier à partir d'une fonction

Bonjour,

J'écris souvent des scripts meta-sql comme celui-ci :

select
       ',' || c.column_name || ' = ' || 's.' || c.column_name
from
        information_schema.tables as t
join
        information_schema.columns as c on t.table_name = c.table_name
join
        information_schema.table_constraints as tc on tc.table_name = t.table_name
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 constraint_type != 'PRIMARY KEY')
and
        t.table_schema = 'public'
and
        c.table_schema = 'public'
and
        t.table_name like 'tblActions_data'
order by
        1;

qui me donne les clauses set pour un update.

J'aimerais mettre ces scripts dans des fonctions, mais je n'arrive pas à trouver comment peut-on écrire dans un fichier texte à partir de pgplsql.

Pourriez-vous me donner une indication ?

Merci pour votre attention.

gleu

Administrateur

Re : Comment écrire dans un fichier à partir d'une fonction

Vous n'avez pas trouvé parce que ça n'existe pas. PL/pgsql est un langage sûr (la version originale est TRUSTED). Un langage trusted est un langage qui permet seulement l'accès à la base de données. Il ne permet pas l'accès aux fichiers ou à une connexion réseau, etc, etc.

Vous pouvez certainement vous en sortir avec la commande COPY. Dans le cas contraire, il faudra ajouter une fonction (certainement écrite en C) qui effectuera cette écriture. Vous pouvez aussi utilisé un langage non trusted comme PL/perlU. Évidemment, dans tous ces cas, votre base de données sera moins sécurisée.

---

Guillaume.

Michel Depiesse

Membre

Re : Comment écrire dans un fichier à partir d'une fonction

Un grand merci pour cet éclaircissement.

J'ai résolu mon problème avec bash mais je reconnais que cette solution passablement baroque pourrait choquer certains esprits épris de classicisme :

#!/bin/bash

psql contacts -qtA -c > tables_pk.txt "select
        kc.table_name || ',' || kc.column_name
from
        information_schema.table_constraints            tc
join
        information_schema.key_column_usage             kc
on
        tc.table_name = kc.table_name
and
        kc.constraint_name = tc.constraint_name
where
        tc.constraint_type = 'PRIMARY KEY'
and
        tc.constraint_schema = 'public'
and
        tc.table_name like 'tbl%data'
order by
        1;"

tables_pk="tables_pk.txt"

while IFS="," read -r table_name pk_name 
do
	echo
	echo
	echo "create or replace function sync.update_$table_name(schema_name varchar, r audit_history) returns void as \$\$"
	echo
	echo "declare stmt varchar; info varchar; pk_name varchar; this_pk integer;"
	echo
	echo "begin"
	echo
	echo "execute format('select \"%s\" from public.\"$table_name\" where audit_id = \$1','$pk_name')
		into this_pk
                using r.audit_id;"
	echo
	echo "if this_pk is null then

                info := format('In update : no record found in \"$table_name\" where audit_id = %s', r.audit_id);

                insert into sync.log values (schema_name, info);
		
		return;"

	echo "end if;"
	echo
	echo "stmt := 'update public.\"$table_name\" as p set"

	set_clause=`psql -qtA contacts -c  " 
        	select
                           '\"' 
			|| c.column_name 
			|| '\"' 
			|| ' = ' 
			|| 's.' 
			|| '\"' 
			|| c.column_name 
			|| '\"' 
			|| ','
		from 
			information_schema.tables as t 
		join
			information_schema.columns as c on t.table_name = c.table_name 
		where
			t.table_schema = 'public' 
		and 
			c.table_schema = 'public' 
		and 
			c.column_name not in ( 
				select
					kcu.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' or constraint_type = 'PRIMARY KEY')
				and 
					tc.table_name = '$table_name'
			)
		and
			c.column_name != 'audit_id'
		and
                        t.table_name = '$table_name'"`

	echo "${set_clause::-1}"	
	echo "';"
	echo
	echo "stmt := stmt || ' from %s.\"$table_name\" as s where p.\"$pk_name\" = s.\"$pk_name\" and p.\"$pk_name\" = \$1';" 
	echo
	echo "execute format(stmt, schema_name) using this_pk;"
	echo
	echo "insert into sync.log values(schema_name, 'Updated \"$table_name\" for \"$pk_name\" = ' || this_pk);"
	echo
	echo "perform sync.update_committed(schema_name, r.id_audit_history);";
	echo
	echo "exception when others then insert into sync.log values(schema_name ,sqlerrm);"
	echo
	echo "end \$\$ language 'plpgsql';"
	echo

done < "$tables_pk"