Summary
Activity
Forums
Tracker
Lists
Tasks
Docs
Surveys
News
SCM
Files

[#1010477] Support for "IN (...)" in SQL queries

View Trackers | Feature Requests | Download .csv | Monitor

Date:
2008-09-19 18:44		 Priority:
3
State:
Open
Submitted by:
Federico Di Gregorio (fog) 		Assigned to:
Nobody (None)
Category:
Group:
Resolution:
None		  
Summary:
Support for "IN (...)" in SQL queries

Detailed description
Right now using the "IN (item1, item2, ..., itemN)" construct in SQL queries requires client-side (C#) formatting of IN arguments. If one has, for example, a list of strings, he should format them into an appropriate list and then:

1) Pass them as a single string parameter;
2) Change the query to use casting to produce an array of items with the right tye;
3) Use the ANY operator and the string_to_array function to do the work usually done by IN.

Some example code is available in the mailing list discussion:

http://pgfoundry.org/pipermail/npgsql-devel/2008-September/000784.html
http://pgfoundry.org/pipermail/npgsql-devel/2008-September/000786.html

This is clumsy, requires the programmer to know the details of C#<->PostgreSQL type conversions (including quoting) and prone to error.

It would be much better to have a wrapper type that initialized with an array can be directly bound to an NpgsqlParameter leaving all the real work to Npgsql itself. Writing a query involving the IN operator would then be as simle as:

string[] ids = new string[3] {"one","two","three"};
NpgsqlCommand cmd = new NpgsqlCommand("SELECT * FROM xxx WHERE id IN (:ids)", conn);
IDbDataParameter param = cmd.CreateParameter();
param.ParameterName = "ids";
param.Value = new NpgsqlSqlIn<string>(ids);
cmd.Parameters.Add(param);
cmd.ExecuteReader();

Followup

Message
Date: 2009-05-12 04:19
Sender: Francisco Figueiredo jr.

One possible solution is to use the any operator.
Your query would be something like:

SELECT product_id, product, qty FROM products where product_id = ANY(:productIdList)

And you would specify your parameter value with a code like this:


command.Parameters.Add(new NpgsqlParameter("productIdList", NpgsqlTypes.NpgsqlDbType.Array | NpgsqlTypes.NpgsqlDbType.Bigint));

command.Parameters[0].Value = proIdList.ToArray();

Check this thread for more info about that.
http://pgfoundry.org/forum/message.php?msg_id=1004933

Date: 2008-12-18 19:10
Sender: Jon Hanna

This would create a strong mismatch between what is happening in C# and what is happening in the database, since IN is not meant to take a single object but a list of them defined in the code (it's a syntactic cousin from the form that takes a subquery). Maybe that's reasonable at an ORM level (whether or not such conversions are reasonable being a big part of the debate on ORMs) and certainly in the data-layer that a business-layer uses, but data-providers should be more prepared to address the database in its terms.

If you've an array (or other collection) in your C# code then why not have the database receive it as such, the following works perfectly against the database used for the NUnit tests:

public void TestArrayAsLookup()
{
    using(NpgsqlConnection conn = new NpgsqlConnection("Server=localhost;User ID=npgsql_tests;Password=npgsql_tests;Database=npgsql_tests;syncnotification=false"))
        using(NpgsqlCommand cmd = new NpgsqlCommand("select * from tablea where field_serial = any (:ids)", conn))
        {
            cmd.Parameters.Add(":ids", new int[]{1, 3});
            conn.Open();
            using(NpgsqlDataReader rdr = cmd.ExecuteReader())
                while(rdr.Read())
                    Console.WriteLine("" + rdr.GetInt32(0) + " " + (rdr.IsDBNull(1) ? "{null}" : rdr.GetString(1)));
        }
}

It doesn't have the mismatch between what's happening at the two levels since both postgres and C# are talking about arrays.

While the objection to having C# deal with IN lists in a completely different way to postgres is largely theoretical, there are practical advantages. The code you give above could only be made to work that way - there's no way to move it into a postgres function. Doing so with the case where postgres is using ANY on an array is trivial to turn into a function:

CREATE FUNCTION array_test(IN integer[]) RETURNS SETOF tablea AS
'select * from tablea where field_serial = any ($1)'
LANGUAGE 'sql' STABLE STRICT;

Now you can change the code in the C# to "select * from array_test(:ids)". It wouldn't be possible with the approach of changing the semantics of IN that you propose.

Attached Files:

Changes:

Field Old Value Date By
summarySupport for "IN (...)" in SQL queries2009-05-12 04:19fxjr
summarySupport for "IN (...)" in SQL queries2008-12-18 19:10talliesin
Powered By FusionForge