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)?

user330315 · Accepted Answer · 2020-09-18 13:01:48Z

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

Jim Jones · Accepted Answer · 2020-09-18 13:02:07Z

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

Collectives™ on Stack Overflow

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

2 Answers 2

Comments

Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Linked

Related