Run stored function for each row and then export to CSV file

Question

I have a users table, and I have a stored function get_user_points(int user_id): int. How can I call this procedure for each row in the user's table, then compare the returned value (amount of points) if it is bigger than 100 and if true add user_id to export in the .csv file (if not bigger than 100 just ignore row)?

score 1 · Accepted Answer · answered Sep 18 '20 at 13:01

Maybe I am missing something, but the following should do what you want:

select user_id
from users 
where get_user_points(user_id) > 100

The process to export that to a .csv file depends on the SQL client you are using. In psql you could e.g. use \copy

score 1 · Answer 2 · answered Sep 18 '20 at 13:02

Use your function in the WHERE clause and put your query inside COPY

COPY (
  SELECT * 
  FROM user_table WHERE get_user_points(int user_id) > 100
) TO '/path/to/file.csv'

Keep in mind that the file will be created in the database server. If client and server are in different machines you have to stick to TO STDOUT or to the option provided by @a_horse_with_no_name

Run stored function for each row and then export to CSV file

2 Answers2