plpgsql function 1

[purek77]

Hi. Two tables:

test=# \dt
List of relations
Schema | Name | Type | Owner
--------+-------------+-------+----------
public | supl_tematy | table | postgres
public | tematy | table | postgres
(2 rows)

first one:

CREATE TABLE supl_tematy (
id integer NOT NULL,
tytul_pracy text,
tytul_ang text,
id_dyd integer NOT NULL
);

test=# select * from supl_tematy ;
id | tytul_pracy | tytul_ang | id_dyd
----+-----------------+-----------------+--------
1 | temat polski 1 | english temat 1 | 1
2 | temat polski 2 | english temat 2 | 1
3 | temat polski 3 | english temat 3 | 2
4 | temat polski 4 | english temat 4 | 2
5 | temat polski 5 | | 1
6 | temat polski 6 | | 1
7 | temat polski 7 | | 1
8 | temat polski 8 | | 1
9 | temat polski 9 | | 1
10 | temat polski 10 | | 1
11 | temat polski 11 | | 2
12 | temat polski 12 | | 2
13 | temat polski 13 | | 2
14 | temat polski 14 | | 2
(14 rows)

second one:

CREATE TABLE tematy (
id integer NOT NULL,
tytul_ang text,
id_dyd integer NOT NULL
);

test=# select * from tematy;
id | tytul_ang | id_dyd
----+------------------+--------
7 | english temat 7 | 1
9 | english temat 9 | 1
12 | english temat 12 | 2
14 | english temat 14 | 2
15 | english temat 15 | 1
16 | english temat 16 | 2
(6 rows)

What I am looking for is an easy way to update supl_tematy from tematy table. I need to fill missing rows in tytul_ang column in supl_tematy table. Final look of supl_tematy table should be like this:

test=# select * from supl_tematy ;
id | tytul_pracy | tytul_ang | id_dyd
----+-----------------+-----------------+--------
1 | temat polski 1 | english temat 1 | 1
2 | temat polski 2 | english temat 2 | 1
3 | temat polski 3 | english temat 3 | 2
4 | temat polski 4 | english temat 4 | 2
5 | temat polski 5 | | 1
6 | temat polski 6 | | 1
7 | temat polski 7 | english temat 7 | 1
8 | temat polski 8 | | 1
9 | temat polski 9 | english temat 9 | 1
10 | temat polski 10 | | 1
11 | temat polski 11 | | 2
12 | temat polski 12 | english temat 12| 2
13 | temat polski 13 | | 2
14 | temat polski 14 | english temat 14| 2
(14 rows)

I think that I should write some function(s) in plpgsql but I have no experience in doing this. Or maybe there is a way to do this in sample update/insert statements ?

Could some1 help ?

 

[feherke]

Hi

Absolutely no need for a function.

[b]update[/b] supl_tematy
[b]set[/b] tytul_ang=tematy.tytul_ang
[b]from[/b] tematy
[b]where[/b] tematy.id=supl_tematy.id [b]and[/b] supl_tematy.tytul_ang [b]is[/b] [b]null[/b];

Feherke.

http://rootshell.be/~feherke/

 

[purek77]

Thanks a lot. It's working I have'nt use pure sql for a long time so I forgot that I can use from statement in a query.