CODEWITHSUNDEEP
postgresql
Khuyagbaatar Batsuren
Khuyagbaatar Batsuren

Reputation: 305

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

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

Upvotes: 13

Views: 33917

Answers (2)

Jim Nasby
Jim Nasby

Reputation: 1268

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.

Upvotes: 3

Juan Carlos Oropeza
Juan Carlos Oropeza

Reputation: 48197

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

Upvotes: 22

Related Questions