Function: Writing unique rows to another table - Mailing list 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@wasp test]$ /usr/local/pgsql/bin/psql test <
func.sql
CREATE FUNCTION
 [kumar@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