PostgreSQL declaring variables with explicit data types

Question

I have read over the documentation and several SO posts, but it still feels unclear how variables are declared and assigned in Postgres... This is what I originally tried to do:

SET SEARCH_PATH = PsychoProductions;
declare var_person_id   INT;
select var_person_id = (
        select id 
        from person 
        where 
                first_name = prm_first_name AND
                last_name = prm_last_name AND
                organization = prm_organization
        );
/* 
Result:
ERROR:  syntax error at or near "INT"
LINE 2:     DECLARE var_person_id   INT;
                                    ^

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

ERROR: syntax error at or near "INT"
SQL state: 42601
Character: 70
*/

It is simple but it doesn't work... I'm kind of new at Postgres syntax, and I was hoping to get some help figuring this out, as I would like to explicitly declare data types, if possible, before setting data to variables.

Do any of you know what the heck I am missing to explicitly declare data types while setting a variable?

EDIT 1: 09-05-2013

Some have pointed out that variables have to be used within a function. I don't believe that's completely the case with Postgres (I know it is with MySQL, and is not with T-SQL) but to the point, here is the above code, in context within a function. I will test what has been pointed out in one answer, shortly, and accept accordingly. So here is the full code so far:

set search_path = PsychoProductions;
create or replace function fcn_Insert_person (
        -- "person" table
        prm_role_id             text, -- table default 'Customer'
        prm_first_name          text,
        prm_last_name           text,
        prm_organization        text,
        prm_website             text,
        prm_default_Billing_Method_ID   text, -- table default 'Net 30'
        prm_active              BOOLEAN,
        -- "address" table
        prm_address_type_id     text, -- table default 'Unique'
        prm_address             text,
        prm_city                text,
        prm_state               text,
        prm_zip_code            text,
        -- "email" table
        prm_email_address       text,
        prm_email_type_id       text, -- table default 'Business'
        -- "phone" table
        prm_phone_number        text,
        prm_phone_type_id       text  -- table default 'Main'
        ) 
        returns setof Person 
as

$delimiter$

begin
set search_patch = PsychoProductions;
start transaction;
insert into person (
        role_id,
        first_name,
        last_name,
        organization,
        website,
        default_billing_method_id,
        active
        )
values (
        prm_role_id,
        prm_first_name,
        prm_last_name,
        prm_organization,
        prm_website,
        prm_default_Billing_Method_ID,
        prm_active
        );
commit; 

start transaction;
declare var_person_id   int;
select var_person_id = (
        select id 
        from person 
        where 
                first_name = prm_first_name AND
                last_name = prm_last_name AND
                organization = prm_organization
        );
-- and this is where I got stuck

EDIT 2: 09-06-2013

I used the select into myVar ... syntax but now I'm getting a completely different problem... Should I ask a new question?

Error:

ERROR:  column "var_person_id" does not exist
LINE 81:         var_person_id,
                 ^
********** Error **********

ERROR: column "var_person_id" does not exist
SQL state: 42703
Character: 2220

Code:

start transaction;
-- declare var_person_id   int;
select id into var_person_id 
        from person 
        where 
                first_name = prm_first_name AND
                last_name = prm_last_name AND
                organization = prm_organization;

insert into address (
        person_id,
        address_type_id,
        address,
        city,
        state,
        zip_code
        )
values (
        var_person_id, -- error here
        prm_address_type_id,
        prm_address,
        prm_city,
        prm_state,
        per_zip_code
        );

As Dwayne pointed out, variables can only be used in procedural code. But why do you need that? If you want to re-use the result of that select later you can do that with a sub-select. If you need it multiple times use a common table expression. Maybe you should show us your real problem. — user330315
– user330315, Commented Sep 6, 2014 at 8:50
I added the code context, thanks @a_horse_with_no_name - Do you think I'm just stuck in T-SQL thinking and there is a more natural method to do this? — Phrancis
– Phrancis, Commented Sep 6, 2014 at 11:50
So you are trying to retrieve the id that was generated by the insert? If that is true, use insert ... values () returning id or use currval() or lastval() to access the last generated sequence value in a subsequent statement. Or use a combination of data modifying CTEs — user330315
– user330315, Commented Sep 6, 2014 at 12:36
I am, and what you suggested will likely work... but, generally, how in the world are variables useful in Postgres, if it is so difficult to use them?? — Phrancis
– Phrancis, Commented Sep 6, 2014 at 13:31
I don't understand what's so difficult in using them. They are useful in the same way as in any programming language. But you can only use them inside a program, i.e. a stored function (this is no different to Oracle, DB2, Firebird and many other DBMS). I still think you are approaching the problem in a wrong way. Instead of asking "how can I use variables" you should explain your real problem. I'm pretty sure there is a much better solution that insisting on using variables in a way they were not meant to. — user330315
– user330315, Commented Sep 6, 2014 at 13:54

Dwayne Towell · Accepted Answer · 2014-09-06 02:51:22Z

7

You can only declare variables inside a function (stored procedure). Try something like this:

CREATE FUNCTION my_func(prm_first_name text,prm_last_name text) 
RETURNS SET OF int $$
DECLARE
    var_person_id int;
BEGIN
    SELECT id INTO var_person_id
        FROM person,var_parameter
        WHERE first_name = prm_first_name 
        AND last_name = prm_last_name
        AND organization = prm_organization;

    -- OR

    var_person_id := (SELECT id FROM ...);

    -- use it later like this:

    RETURN QUERY SELECT ... FROM ... WHERE id=var_person_id;
END;
$$ LANGUAGE PLPGSQL;

edited Sep 6, 2014 at 2:51

answered Sep 6, 2014 at 2:38

Dwayne Towell

8,6635 gold badges39 silver badges54 bronze badges

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

6 Comments

200_success Over a year ago

But you never demonstrated use of the var_person_id variable.

Phrancis Over a year ago

@DwayneTowell I added the rest of my code as an edit, I have been trying to declare within a function. The only problem I have with your method is that SELECT ... INTO myVar infers the data type, as opposed to declaring it explicitly, and that is what I was trying to do... But if there is a more "Postgres" way to do this, I'm open. I guess I'm stuck in T-SQL/MySQL mode for the moment and perhaps not grasping a core concept of Postgres...

user330315 Over a year ago

@Phrancis: what do you mean with "infers the data type". Any variable you use must be declared with a data type. PL/pgSQL is a strongly type language (as is SQL). You can't declare variables without a type. Specifying the type shouldn't be a problem because the column you are select also has a type. The variable needs to have the same (or compatible) type.

Phrancis Over a year ago

I'm trying to explicitly declare a data type on the variable; where if I tell the engine this var should be an int and instead it is passed 'foo' it won't take it...

user330315 Over a year ago

@Phrancis: of course you can't. 'foo' is a character literal not an integer. So why on earth would you want to store a character value to a variable defined as a number? Dwayne's answer clearly shows you how to do what you apparently want to do.

|

Collectives™ on Stack Overflow

PostgreSQL declaring variables with explicit data types

EDIT 1: 09-05-2013

EDIT 2: 09-06-2013

1 Answer 1

6 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

EDIT 1: 09-05-2013

EDIT 2: 09-06-2013

1 Answer 1

6 Comments

Your Answer

Sign up or log in

Post as a guest

Related