plpgsql, variable inside query in function

Question

I am new with plpgsql. I create new function

CREATE OR REPLACE FUNCTION createObj(number integer)
RETURNS INTEGER AS
$$
BEGIN

END;
$$

I have problem that if I want to make query in the body of the function and use in the query the the number variable while in the table their is a number the boolean is always true.

something like:

 Select * from objects O, where O.number=number...

so number is not the number of the function but the filed in the table. Their is a way to implement this and dont change the variable name?

Gordon Linoff · Accepted Answer · 2015-11-25 02:18:33Z

1

Define your parameters with a prefix to distinguish them from columns:

CREATE OR REPLACE FUNCTION createObj(in_number integer)
RETURNS INTEGER AS
$$
BEGIN
    Select * from
    objects O
    where O.number = in_number...
END;

$$

answered Nov 25, 2015 at 2:18

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

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

2 Comments

user5303752 Over a year ago

thanks, I though that their is a way to take the original variable.. Thanks anyway .

Gordon Linoff Over a year ago

@Alon . . . You can use $1. I think it is just a good habit to use parameter and variable names that can't be confused with other things.

Collectives™ on Stack Overflow

plpgsql, variable inside query in function

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related