×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Jobs

plpgsql function

plpgsql function

plpgsql function

(OP)
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 ?
 

RE: plpgsql function

Hi

Absolutely no need for a function.

CODE

update supl_tematy
set tytul_ang=tematy.tytul_ang
from tematy
where tematy.id=supl_tematy.id and supl_tematy.tytul_ang is null;

Feherke.
http://rootshell.be/~feherke/

RE: plpgsql function

(OP)
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.

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close