Welcome to OGeek Q&A Community for programmer and developer-Open, Learning and Share
Welcome To Ask or Share your Answers For Others

Categories

0 votes
1.2k views
in Technique[技术] by (71.8m points)

postgresql - Postgres integer arrays as parameters?

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?

See Question&Answers more detail:os

与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
Welcome To Ask or Share your Answers For Others

1 Reply

0 votes
by (71.8m points)

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

与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
OGeek|极客中国-欢迎来到极客的世界,一个免费开放的程序员编程交流平台!开放,进步,分享!让技术改变生活,让极客改变未来! Welcome to OGeek Q&A Community for programmer and developer-Open, Learning and Share
Click Here to Ask a Question

...