SELECT script is easy to be obtained from PostgreSQL tables by using the GUI such as pgAdmin. By right-clicking on a table and then select the SELECT script, we get the script. Another way could be affordable by the coding.
First, create a table.CREATE TABLE coba
(
pk character(12) NOT NULL,
nourut smallint NOT NULL,
nourutku smallint NOT NULL,
unit_id character(3) NOT NULL,
CONSTRAINT coba_pkey PRIMARY KEY (pk)
)
WITH (OIDS=FALSE);
ALTER TABLE coba OWNER TO binsar;
List the table's fields:
SELECT column_name FROM information_schema.columns WHERE table_name = 'coba'
Output:
"pk"
"nourut"
"nourutku"
"unit_id"
Another way...using codingSELECT script can be generated through the following script:
SELECT ' SELECT ' || array_to_string(ARRAY(SELECT '' ||column_name
FROM
information_schema.columns WHERE table_name = 'coba'), ',') || ' FROM binsar.coba;';
Output:
SELECT pk,nourut,nourutku,unit_id FROM binsar.coba;
We may face the need to change the primary key or adding a new primary key based on the existing rows at the same table. The problems will be encountered, especially if there are many relationships between tables. For example, how do we simply insert some rows and only change the primary key value?
insert into binsar.coba VALUES ('1',1, 1,'1');
insert into binsar.coba SELECT '2',2, 2,'2';
insert into binsar.coba SELECT '3', nourut, nourutku, unit_id FROM binsar.coba WHERE pk='1';
The last command simply solve the need....certainly for a table with some fields.
If I have many tables and fields, ( think about fields name: new, dropped or modified ),
I was not diligent enough to do it. Were you?
Here the script:
SELECT 'SELECT ''300'',' || array_to_string(ARRAY(SELECT '' || column_name
FROM information_schema.columns
WHERE table_name = 'coba'
AND column_name NOT IN('pk')
), ',') || ' FROM binsar.coba where pk=''1'';';
Output:
SELECT '300',nourut,nourutku,unit_id FROM binsar.coba where pk='1';
So, new rows can be added with ease. We do not need to write all the field names,
just need to write down the field we do not want, also without hard coding the SELECT script.
In a stored function (PL/pgSQL), I use EXECUTE 'statement' for all tables in a database.
CREATE OR REPLACE FUNCTION cobadeh(pk character, newpk character,tabname character)
RETURNS character AS
$BODY$
DECLARE
t1 character varying (300);
t2 character varying (300);
BEGIN
t1='INSERT INTO binsar.' || tabname;
SELECT ' SELECT ' || '''' || newpk|| '''' || ',' || array_to_string(ARRAY(SELECT '' || column_name
FROM information_schema.columns
WHERE table_name = 'coba'
AND column_name NOT IN('pk')
), ',') || ' FROM binsar.coba WHERE pk=' || '''' ||pk ||'''' INTO t2;
EXECUTE t1 || t2;
RETURN t1 || t2;
END;
$BODY$
LANGUAGE 'plpgsql' VOLATILE
COST 100;
ALTER FUNCTION cobadeh(character,character,character) OWNER TO binsar;
then tests it:
select * from binsar.cobadeh('1','300', 'coba');
select * from binsar.cobadeh('3','ABC', 'coba');
will run these command:
"INSERT INTO binsar.coba SELECT '300',nourut,nourutku,unit_id FROM binsar.coba WHERE pk='1'"
INSERT INTO binsar.coba SELECT 'ABC',nourut,nourutku,unit_id FROM binsar.coba WHERE pk='3'
The table now has new rows:
select * from binsar.coba ;
"1 ";1;1;"1 "
"2 ";2;2;"2 "
"3 ";1;1;"1 "
"300 ";1;1;"1 "
"ABC ";1;1;"1 "
that's.
