Postgres 14

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

-- DROP TABLE IF EXISTS pg_temp.check_table;
CREATE TEMP TABLE check_table(row_num serial, "Document Number" text, x_x int, y_y text);
INSERT INTO check_table("Document Number", x_x, y_y) VALUES
('A',202000,'JASE'),
('A',202100,'JASE'),
('A',638010,'JASE'),
('A',NULL,'JASE'),
('A',NULL,'JASE'),
('A',202100,'JASE'),
('A',638000,'JASE'),
('A',202100,'JASE'),
('B',638000,'JASE'),
('B',202100,NULL),
('B',638000,'JASE'),
('B',NULL,'ARAS'),
('B',638000,'ARAS'),
('B',NULL,'ARAS'),
('B',638000,NULL),
('B',124004,NULL);

TABLE check_table;
16 rows affected



row_num
Document Number
x_x
y_y


1
A
202000
JASE

2
A
202100
JASE

3
A
638010
JASE

4
A
null
JASE

5
A
null
JASE

6
A
202100
JASE

7
A
638000
JASE

8
A
202100
JASE

9
B
638000
JASE

10
B
202100
null

11
B
638000
JASE

12
B
null
ARAS

13
B
638000
ARAS

14
B
null
ARAS

15
B
638000
null

16
B
124004
null


-- DROP FUNCTION alt_edger(text, text, text, text, text)
CREATE OR REPLACE FUNCTION alt_edger(_s text, _v text, _relation text, _tbl text, _tbl_src text, OUT row_count int)
  LANGUAGE plpgsql AS
$func$
DECLARE
   _sql text := format(
       'INSERT INTO pg_temp.%3$I (label, source, target)
        SELECT DISTINCT $1, %1$I, %2$I FROM pg_temp.%4$I
        WHERE (%1$I, %2$I) IS NOT NULL'
      , _s, _v, _tbl, _tbl_src);
BEGIN
   -- RAISE NOTICE '%', _sql;  -- debug
   EXECUTE _sql USING _relation;
   GET DIAGNOSTICS row_count = ROW_COUNT;  -- return number of inserted rows
END
$func$;
-- create a temporary table and execute the function twice
-- DROP TABLE IF EXISTS pg_temp.temp_stack;
CREATE TEMP TABLE temp_stack(label text, source text, target text, attr text, graph text);

SELECT alt_edger('x_x', 'y_y', ':associated_with', 'temp_stack','check_table');
SELECT alt_edger('Document Number', 'x_x', ':documents', 'temp_stack', 'check_table');

TABLE pg_temp.temp_stack;


alt_edger


5




alt_edger


7




label
source
target
attr
graph


:associated_with
202000
JASE
null
null

:associated_with
638000
JASE
null
null

:associated_with
638000
ARAS
null
null

:associated_with
638010
JASE
null
null

:associated_with
202100
JASE
null
null

:documents
A
202000
null
null

:documents
B
202100
null
null

:documents
A
638010
null
null

:documents
B
124004
null
null

:documents
A
202100
null
null

:documents
A
638000
null
null

:documents
B
638000
null
null

row_num	Document Number	x_x	y_y
1	A	202000	JASE
2	A	202100	JASE
3	A	638010	JASE
4	A	null	JASE
5	A	null	JASE
6	A	202100	JASE
7	A	638000	JASE
8	A	202100	JASE
9	B	638000	JASE
10	B	202100	null
11	B	638000	JASE
12	B	null	ARAS
13	B	638000	ARAS
14	B	null	ARAS
15	B	638000	null
16	B	124004	null

label	source	target	attr	graph
:associated_with	202000	JASE	null	null
:associated_with	638000	JASE	null	null
:associated_with	638000	ARAS	null	null
:associated_with	638010	JASE	null	null
:associated_with	202100	JASE	null	null
:documents	A	202000	null	null
:documents	B	202100	null	null
:documents	A	638010	null	null
:documents	B	124004	null	null
:documents	A	202100	null	null
:documents	A	638000	null	null
:documents	B	638000	null	null