Skip site navigation (1) Skip section navigation (2)

Function: Writing unique rows to another table

From: Kumar S <ps_postgres(at)yahoo(dot)com>
To: pgsql-novice(at)postgresql(dot)org
Subject: Function: Writing unique rows to another table
Date: 2004-09-28 18:16:20
Message-ID: 20040928181620.58448.qmail@web51408.mail.yahoo.com (view raw or flat)
Thread:
Lists: pgsql-novice
Dear Group, 
 I have 3 tables where :
f_huge = a table with variations. Strictly speaking,
the data is not identical, however, keeping aside the
spelling mistakes the data with same f_huge_name
column value it is identical. 

f2_unique = This table has unique names that are there
in f_huge. 

f3 : an empty table with exact columns where I wanted
to insert unique elements from f_huge to go into this
table. 


My tables look like this:
F_HUGE TABLE:

test=# select * from f_huge;
 f_huge_id | f_huge_name | f_huge_badge | f_huge_city
|    f_huge_edu
-----------+-------------+--------------+-------------+-------------------
         2 | Apparao     | A12345       | Anakapalli 
| Brown University
         3 | Paparao     | A23456       | Madugula   
| Town University
         4 | Kanakarao   | A56788       | Pisinikada 
| Temple University
         5 | Chinnarao   | A34456       | Uppalada   
| Sink University
         6 | Gangaraju   | B34657       | Srikakulam 
| Kulam University
         7 | Paparao     | A23457       | Madugula   
| Towne University
         8 | Kanakarao   | A56788       | Pisanikada 
| Temple University
         9 | Appalaraju  | A34457       | Chodavaram 
| AMAL college
        10 | Appalaraju  | A45678       | Chowdavaram
| AMAL College
(9 rows)


F2_UNIQUE:
test=# select * from f2_unique;
 f2_id |  f2_name
-------+------------
     1 | Apparao
     2 | Paparao
     4 | Chinnarao
     5 | Gangaraju
     7 | Appalaraju
     8 | Kanakarao
(6 rows)



I wrote a function where I take each value from
f2_name column and search it in f_huge. Function will
hit the fist element in f_huge, then f(x) will write
all the columns data in f_huge into new_table.  After
this the FOR loop will break and then the second
element is taken and searched.  
e.g: I wanted my f(x) to pick 'Apparao'
(f2_unique.f2_name) and search it in f_huge. It finds
it in f_huge_name now, I wanted my f(x) to write
f_huge row data (Apparao,A12345,Anakapalli,Brown
University) into new_table. After it write the
function exists from if loop (here I wrote BREAK
because I do not know syntax in pl/pgsql) and searces
the next value from f2_unique.

The function I wrote is here:

CREATE FUNCTION duplic_insert(char) RETURNS bool AS '
        DECLARE
                inchar ALIAS for $1;
                colval record;
                tmp char;
                result int4;
        BEGIN
                result = 0;
                FOR colval in SELECT f_huge_name FROM
f_huge LOOP
                        tmp := colval.name;
                        IF tmp = inchar THEN
                                INSERT INTO
new_table(f_huge_name,f_huge_badge,f_huge_city,f_huge_edu)
                                VALUES( SELECT * from
f_huge where f_huge_name = tmp;);
                                RETURN ''t'';
                                BREAK;
                        ELSE;
                                result := result + 1;
                        END IF;
                END LOOP;
        RETURN result;
        END ;
'LANGUAGE'plpgsql';


I tried executing this function. 

 [kumar(at)wasp test]$ /usr/local/pgsql/bin/psql test <
func.sql
CREATE FUNCTION
 [kumar(at)wasp test]$



When I run using:

test=# select duplic_insert(select * from f2_unique;);
ERROR:  syntax error at or near "select" at character
22


I know many things here are wrong (syntax and usage). 
Can any one please help me to get this thing right. I
was suggested to post the code and other things.

Looking forward for some help. 

thank you. 

Kumar.


		
_______________________________
Do you Yahoo!?
Declare Yourself - Register online to vote today!
http://vote.yahoo.com

pgsql-novice by date

Next:From: philDate: 2004-09-28 21:26:16
Subject: Brand New User (I hope)
Previous:From: Taber, MarkDate: 2004-09-28 18:16:14
Subject: Re: pg 8.0b2, psql problem

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group