0

I am new to stored procedure in postgres. I know how to write my procedure but it will not work as I want. Let me explain my scenario what kind of output and what I want to achieve.

I am using postgres 8.1 version.

result  = select u.name from users u where u.designation = 'S/w Engg';
select count(c.id) from cars c where c.name in (results)
totals = select count(result)

Here are the 3 steps which I want to write in stored procedure.

In first step I want to store the output of that query which return multiple rows with one column.

In second step, I want to execute another query in which I want to used 1st query result as 'IN' and store again in variable.

In last step, I want to count the no of rows getting from 1st query result.

So, can anyone help me out to write the stored procedure for above conditions.

Kushal Jain
  • 3,029
  • 5
  • 31
  • 48
  • If you're new: learn right from the beginning that it's a **STORED** procedure - as in a procedure that's **stored** within your database. It has nothing to do with a "store" .... – marc_s Apr 03 '17 at 12:16
  • @marc_s I want to reuse the values from the query 'select col1 from table;', the query will give me multiple results, now i want to use the results as multiple subqueries. I feel I am missing how to declare the variable which is in memory. I can acheive the same using temp table but that seems like an expensive approach. Any help will be appreciated. – Kushal Jain Apr 03 '17 at 12:21
  • @KushalJain (result-) sets cannot be stored in a variable. [arrays](https://www.postgresql.org/docs/current/static/arrays.html) can. -- in this case you'll need to use [array-specific functions](https://www.postgresql.org/docs/current/static/functions-array.html), like `array_length()`. -- another workaround is to use [temporary tables](https://www.postgresql.org/docs/current/static/sql-createtable.html). – pozs Apr 03 '17 at 12:52
  • BTW 8.1 is very archaic (and doesn't supported anymore). you should really consider upgrading. – pozs Apr 03 '17 at 12:53
  • @pozs I cannot directly store query results in variables ? I need to take result into arrays ? Temp table is slow because I have to read millions of records as first millions of records read then write in temp table then again read to use!!! – Kushal Jain Apr 03 '17 at 13:07
  • @KushalJain if you need to work with millions of records, then you could repeat the query where it is needed (there is no point to assign millions of values to a variable anyway). F.ex. `select count(c.id) from cars c where c.name in (select u.name from users u where u.designation = 'S/w Engg')` -- another workaround is to use [cursors](https://www.postgresql.org/docs/current/static/plpgsql-cursors.html) (but that won't be appealing in every case). – pozs Apr 03 '17 at 13:10
  • If you upgrade to PostgreSQL 9.1 then you can create tablespace pointing at tmpfs and create `UNLOGGED` table that will be used as your tmp table. – Łukasz Kamiński Apr 03 '17 at 13:19
  • @ŁukaszKamiński Ok. But My application is huge so its not possible to test whole application thoroughly!!! Can you tell me that all the syntax and query that is used in 8.1 will be same in 9.1 version ? – Kushal Jain Apr 03 '17 at 13:30
  • Sadly I don't know how to migrate 8.1 to 9.1. You could use CTE/Views and just repeat query like pozs suggested. If you want to use a lot of memory in this function you could change some of the server configuration properties and assign more memory, like for example `CREATE FUNCTION [..] SET work_mem = '128MB'; [..] AS $body$ [..]` – Łukasz Kamiński Apr 03 '17 at 13:42

0 Answers0