How to declare variable and assign value into that in postgresql?

Question

I'm very new in postgresql. I read many posts in this questions, but still don't get correct answer in my simple problem and keep receiving syntax error. I'm trying declare new string variable named parents_d and in the following lines trying to assign new value as well. Please help me!

CREATE OR REPLACE FUNCTION retrieve_parents(cid integer) RETURNS text AS $$ 
BEGIN
    DECLARE pd text;    
    pd:= 'function';
    RETURN concat(cid,pd);
END;
$$ LANGUAGE plpgsql;

ERROR: duplicate declaration at or near "pd" LINE 4: pd:= 'function'; ^

********** Error **********

ERROR: duplicate declaration at or near "pd" SQL state: 42601 Character: 104

what error you get? Please read How-to-Ask And here is a great place to START to learn how improve your question quality and get better answers. — Juan Carlos Oropeza
– Juan Carlos Oropeza, Commented Mar 4, 2016 at 2:04
@JuanCarlosOropeza Do you know how to declare the variable and assign value in postgresql? — Khuyagbaatar Batsuren
– Khuyagbaatar Batsuren, Commented Mar 4, 2016 at 2:09
Actually I tried many times in different options. Always receiving some errors. There is now duplicate error. I'm sorry. I know it is very stupid basic question. — Khuyagbaatar Batsuren
– Khuyagbaatar Batsuren, Commented Mar 4, 2016 at 2:27

Juan Carlos Oropeza · Accepted Answer · 2016-03-04 02:29:41Z

22

try like this

SQL Fiddle Demo

CREATE FUNCTION retrieve_parents(cid integer) RETURNS text AS $$
DECLARE pd text;    
BEGIN

    pd:= 'function';
    RETURN concat(cid,pd);

END; $$
LANGUAGE PLPGSQL

answered Mar 4, 2016 at 2:29

Juan Carlos Oropeza

48.4k14 gold badges87 silver badges128 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Khuyagbaatar Batsuren Over a year ago

Thank you very much. It is working great! Can I ask you one more question? Is pd a local variable or global? If the function is recursive, is it fine?

Juan Carlos Oropeza Over a year ago

Yes, is local. If the function is recursive should be ok. You can always use RAISE NOTICE 'pd value (%)', pd; to print the value for debugging

Khuyagbaatar Batsuren Over a year ago

I see. Thank you very much. :) You are life saver.

Jim Nasby · Accepted Answer · 2016-03-04 03:13:31Z

3

I tried to do this as an edit, but the edit was rejected as being too small.

The problem you're running into is a misunderstanding of plpgsql's (somewhat confusing) block syntax. If you look on that page, the critical part you're missing is this:

[ DECLARE
    declarations ]

There can be multiple declarations in a single DECLARE section. You can also nest blocks:

DECLARE
  c_pi CONSTANT double precision := pi();
  v_text text;
BEGIN
  DECLARE
    v_blah text;
  BEGIN
    NULL;
  END;
END;

Note that the semicolon is optional on the outer-most block.

answered Mar 4, 2016 at 3:13

Jim Nasby

1,27810 silver badges11 bronze badges

Collectives™ on Stack Overflow

How to declare variable and assign value into that in postgresql?

2 Answers 2

3 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related