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
2 | Apparao | A12345 | Anakapalli


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_h uge_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

---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faqs/FAQ.html