Using a text file in the where clause of postgreSQL query

Question

Is this possible? Basically, we are going to be using a script to extract information from our database. We are trying to design a query that could use a list of values in a text file in the WHERE clause to limit to specific records. This list will change regularly and it will not be efficient to manually edit the query on a regular basis. I looked at this question for SQL Server and I want to do something similar in PostgreSQL.

So, for example, a super simple query might be something like

SELECT * 
FROM users
WHERE userID in "C:\myfile.txt";

No that's not possible. You need to import the file into a (temporary) table first. An alternative is to define a foreign data wrapper that lets you access the file as a table (but then the file needs to be on the server where Postgres is running) — user330315
– user330315, Commented May 5, 2015 at 14:18

Shawn · Accepted Answer · 2015-05-05 20:22:36Z

2

You could definitely do this natively. If going that route, I would employ the Postgres COPY function along with a temp table in your query, discarding the temp table when finished. If I'm not mistaken, this would require that your file be present inside of a folder that Postgres has control over, such as your data folder, wherever you have Postgres installed.

However, for a cleaner look, I would prefer employing PL/R for something like this. You can quickly read from the file and return an array of values to use in your query. I'm sure you can substitute PL/R with PL/PYTHON or whatever else you prefer that has methods for accessing external files.

CREATE FUNCTION file_vals()
  RETURNS integer[] AS
  $BODY$
    return (readLines('C:/path/to/your/file.txt'))
  $BODY$
    LANGUAGE plr IMMUTABLE;

Your file.txt looks like:

555
123
567

Then call from your function (I put sample data in a subquery to simplify):

WITH users AS(
  SELECT 123 AS userID
)
SELECT userID
FROM users
WHERE userID = ANY(file_vals())

Edit: As DanielVérité pointed out in the comments, it's important to note that this only works if you have admin privileges over your database. PL/R and any other language extension that gives you external file access will inherently be an untrusted language, which means only admins can create functions in those languages.

It's also important to note that the file you're reading from must be accessible directly from the Postgres server. If you're executing these queries via remote client, you'll need to get that file over to the server first.

edited May 5, 2015 at 20:22

answered May 5, 2015 at 14:51

Shawn

17211 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Daniel Vérité Over a year ago

You must be superuser to do that. Since you mention plpython, see the fine print at the bottom: PL/Python is only available as an "untrusted" language...Only superusers can create functions in untrusted languages such as plpythonu. There are no trusted languages that can operate on the file system.

Shawn Over a year ago

@DanielVérité Agreed. PL/R is the same. OP did not specify any security requirements, so I did not take that into account with my answer.

Jorge Campos Over a year ago

I agree with Shawn here. But I also think that the information provided by Daniel should be added on the answer. +1 anyway, good answer.

Collectives™ on Stack Overflow

Using a text file in the where clause of postgreSQL query

1 Answer 1

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related