Tell me more ×
Stack Overflow is a question and answer site for professional and enthusiast programmers. It's 100% free, no registration required.
up vote 5 down vote favorite
2

I understand that in Postgres pure, you can pass an integer array into a function but that this isn't supported in the .NET data provider Npgsql.

I currently have a DbCommand into which I load a call to a stored proc, add in a parameter and execute scalar to get back an Id to populate an object with.

This now needs to take n integers as arguments. These are used to create child records linking the newly created record by it's id to the integer arguments.

Ideally I'd rather not have to make multiple ExecuteNonQuery calls on my DbCommand for each of the integers, so I'm about to build a csv string as a parameter that will be split on the database side.

I normally live in LINQ 2 SQL savouring the Db abstraction, working on this project with manual data access it's all just getting a bit dirty, how do people usually go about passing these kinds of parameters into postgres?

share|improve this question

2 Answers

up vote 7 down vote accepted

See: http://www.postgresql.org/docs/9.1/static/arrays.html

If your non-native driver still does not allow you to pass arrays, then you can:

  • pass a string representation of an array (which your stored procedure can then parse into an array -- see string_to_array)

    CREATE FUNCTION my_method(TEXT) RETURNS VOID AS $$ 
DECLARE
       ids INT[];
BEGIN
       ids = string_to_array($1,',');
       ...
END $$ LANGUAGE plpgsql;

    then

    SELECT my_method(:1)

    with :1 = '1,2,3,4'

  • rely on Postgres itself to cast from a string to an array

    CREATE FUNCTION my_method(INT[]) RETURNS VOID AS $$ 
       ...
END $$ LANGUAGE plpgsql;

    then

    SELECT my_method('{1,2,3,4}')

  • choose not to use bind variables and issue an explicit command string with all parameters spelled out instead (make sure to validate or escape all parameters coming from outside to avoid SQL injection attacks.)

    CREATE FUNCTION my_method(INT[]) RETURNS VOID AS $$ 
       ...
END $$ LANGUAGE plpgsql;

    then

    SELECT my_method(ARRAY [1,2,3,4])
share|improve this answer
Thanks! No I hadn't, I was relying on what my colleagues had told me. I'll give it a shot. – TreeUK Feb 20 '09 at 23:15
Do you have any proof regarding lack of native support of prepared statements in PG? I couldn't find any mentions of that fact in the manual. As for logging, I guess that server logs the queries as they are executed not as they are passed from the client thus no "EXECUTE" statements in the log. – Ihor Kaharlichenko Aug 13 '12 at 14:33
@IhorKaharlichenko the wording was poor, and was off-topic. Edited the reply. Re. the original claim, drivers will not use PREPARE unless server-side prepared statements are enabled (sometimes explicitly) and, in some cases, unless a certain number-of-executions threshold has been exceeded for a command. If command logging is enabled in postgresql.conf then you will see the actual SELECT/INSERT/etc. commands when server-side prepared statements are NOT used. – vladr Aug 13 '12 at 18:11
up vote 5 down vote

I realize this is an old question, but it took me several hours to find a good solution and thought I'd pass on what I learned here and save someone else the trouble. Try, for example, 

SELECT * FROM some_table WHERE id_column = ANY(@id_list)

where @id_list is bound to an int[] parameter by way of

command.Parameters.Add("@id_list", NpgsqlDbType.Array | NpgsqlDbType.Integer).Value = my_id_list;

where command is a NpgsqlCommand (using C# and Npgsql in Visual Studio).

share|improve this answer
Useful info, thanks. – TreeUK Oct 12 '11 at 8:50

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.