0

I am using the PostgreSQL version 12 database server and development in c# using visual studio. I want to move my old SQL server procedures to the PostgreSQL database. But the old procedures return a result set that's not possible to do the same in PostgreSQL. The PostgreSQL needs to write that logic into a function and then this function call into a procedure that creates on the PostgreSQL database.

I found a link for reference:

  1. http://www.sqlines.com/postgresql/how-to/return_result_set_from_stored_procedure#:~:text=PostgreSQL-,PostgreSQL%20%2D%20How%20to%20Return%20a%20Result%20Set%20from%20a%20Stored,CREATE%20FUNCTION%20statement%20in%20PostgreSQL.&text=To%20return%20one%20or%20more,to%20use%20refcursor%20return%20type.

  2. https://stackoverflow.com/a/60094734/10450078

how can I return the result set from the stored procedure in PostgreSQL?

Thanks In Advance.

Improve this question
6
  • Procedures aren't meant to return results. If you want to return a result set, use a set returning function. Postgres isn't SQL Server. I also don't understand why you would need a procedure to call a function. Just call the function from your c# code: select * from function_returning_result(...) Commented Oct 17, 2020 at 10:54
  • Thanks, @a_horse_with_no_name. If I have 300 or more SQL server procedure that returns a result set then I have to do convert/write as a function in PostgreSQL and after that call within the procedure. Commented Oct 17, 2020 at 10:59
  • @priyanka no need to call the postgresql function in postgresql procedure. you can directly call your function in your code. Because if you call your function in the procedure then there is no meaning to use function. PostgreSQL Function is very powerful and can be used in place of SQL Server SP. Commented Oct 17, 2020 at 11:03
  • @AkhileshMishra thank you for help. Now I got the point. But this function can I used for insert/update operation on a database or write a procedure for that. Commented Oct 17, 2020 at 11:11
  • Yes you can use function returning void for insert/update operations same as SP. Commented Oct 17, 2020 at 11:14
Related questions
0
php - plpgsql calling stored procedure and fetching result set
2
PostgreSQL Stored Procedure, How to Return Result Set
0
Return ResultSet in pgsql function
Load 7 more related questions Show fewer related questions

0

Reset to default

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.