postgre function

[lion cave]

Hi,

I have a problem regarding on making function in postgre.

I am newbie for this.

My problem is that i want to make a function in postgre that accepts the name of the table. In the processed of the function, the table name inputted should be merge to the another table. And, the result values of query returned.


Any idea for this.

Kindly show the code also. And, if you know any referrence material for the procedural language such as the reserved words or keywords in postgresql, or any sample code, pls, put it the link also. thanks.

Please help.

I highly appreciate for your help. :)

[rski]

Looks simple but what do you mean

In the processed of the function, the table name inputted should be merge to the another table.

What is another table?

And, the result values of query returned.

You mean the concatenation should be outputted?

The best manual is at postgresql.org

[lion cave]

hi,

thanks for the reply..

Ok, i'll explain clearly of what i want to say. My problem is to make a function in Postgre that function is to accepts a parameter which the name of the table.
The process of this function is read the table name that passed and make a query of that table joining with another table that is already defined in this function. This function would return the rows result of this query.

example:

function : my_function(pas_table)
body :
/* make some processed */

Select * from pass_table inner join another_table using(id)
return query results;
end:

This function should return the results of the query from a joined tables.

May ask what type of return shoud i used for this?

I am newbie for this sql programming.
sorry, if i can't explain well.

Pls, help me.:)

[lion cave]

Help me for this pls......

I'm trying to search what kind of return type in sql that would return the query results in joined table.

CREATE OR REPLACE FUNCTION "public"."view_table" (table_1 text) RETURNS SETOF "public"."table_2" AS
$body$
DECLARE
r table_2%rowtype;
BEGIN
FOR r IN SELECT * FROM table_2 LOOP
RETURN NEXT r;
END LOOP;
END
$body$
LANGUAGE 'plpgsql'
VOLATILE
CALLED ON NULL INPUT
SECURITY INVOKER
COST 100 ROWS 1000;

Select * from view_table('table1'')

This is a sample code but this would only return a rows for table_2. I am not finished to implement it. I want to join the table_1 and table_2 and would return their query results. But i don't know yet what is the return type to use for these two query.
Additionaly, I also don't know how to cancatenate the value from parameter which the table_1 to the Select statement in order to make a sql statement such as

Select * from table_1 inner join table_2 using(thier_id)

Kindly, help me for this....

I appreaciate a lot for any help, suggestion, idea and comment.

Thanks :)

[rski]

You should use 'setof record' as the return type.

To do a dynamic select statement use EXECUTE statement

for r in EXECUTE 'Select * from '||table_1||' inner join table_2 using(thier_id)' 
 

but r should be a record type.

You'll see that working with records isn't simple, so it is worth to do such a function? Do you really need that function, maybe there is a better, simpler solution.