How to declare a variable and set value of postgress pgsql

Question

0.00/5 (No votes)

See more:

i set a value in declared variable p_emp_id to get max emp_id from employee table

as select max_id into a declared variable but not getting a result set of select statement

What I have tried:

SQL

CREATE OR REPLACE FUNCTION public.max_emp_id_select(
    
    )
  RETURNS TABLE(p_emp_id integer) AS
$BODY$

declare p_emp_id integer;
BEGIN
   RETURN QUERY 


select (COALESCE(max(new_emp_id),0) + 1) into p_emp_id
from Employee


select p_emp_id //here not getting variable value
end

END;

$BODY$
  LANGUAGE plpgsql VOLATILE

Posted 2-Apr-18 0:06am

Member 13140552

Updated 3-Apr-18 8:36am

Wendelius

v3

Add a Solution

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Wendelius · Accepted Answer · 2018-04-02T00:48:00

Solution 1

Not sure if I understand the question correctly but should you return only a single value. Something like

SQL

CREATE OR REPLACE FUNCTION max_emp_id_select() RETURNS integer AS 
$BODY$
DECLARE
   p_emp_id integer;
BEGIN
   SELECT COALESCE(max(emp_id),0)
   INTO p_emp_id
   FROM Employee;

   RETURN p_emp_id + 1;
END;
$BODY$ 
LANGUAGE plpgsql;

Sorry for any typos. It's been years since I last used pgplsql...

ADDED
------
Have a try with

SQL

UPDATE employee1 
SET emp_id = max_emp_id_select()
WHERE ...

Posted 2-Apr-18 0:48am

Wendelius

Updated 3-Apr-18 8:31am

v2

Comments

Member 13140552 2-Apr-18 8:58am

this is something right but we want this variable value to nested query like getting max number and update another table to this variable value

Wendelius 2-Apr-18 10:16am

Can you post an example how you're going to use this function?

Member 13140552 3-Apr-18 6:01am

SELECT COALESCE(max(emp_id),0)
INTO p_emp_id
FROM Employee;
update employee1 set emp_id = p_emp_id //here variable p_emp_id cannot access to update emp_id and also not working into p_emp_id

Wendelius 3-Apr-18 14:34pm

Not sure if I understand your comment correctly, but can you use the function in the statement? See the updated example.