Cannot run prepared statement/function in postgres

Question

How to get a row in a temp variable and process/use its fields?? See the begin section, I need to get account info, do some calculations, like I need to get value of Account.field1 - Account.Field2 in accountbalance method , how to do that?? --this is statement

PREPARE get_account (varchar) AS
SELECT * FROM "Accounts" WHERE "AccountKey" = $1 LIMIT 1;

-- Try to run directly
select EXECUTE(get_account("A200"));


--Created a function and used statement. 
CREATE OR REPLACE FUNCTION accountbalance(VARCHAR) RETURNS REAL AS $$
    DECLARE
    AKey ALIAS FOR $1;  
    balance REAL;
    account RECORD;
BEGIN   
   account := EXECUTE(get_account("A200"));
   --Tried these too
   --account := EXECUTE get_account('A200');    
   --account := EXECUTE get_account("A200");    
   --I need to get account data here, process, How to get data to a declared variable, how user specific column, May be something like Accounts."Total".. 
   --I tried to run direct query here and get data to account, but no success so tried prepared statement. 
   --I will be doing complex calculations here, trying to return a column for test , not sure is it correct? 
RETURN account.Actual;
END;
$$ LANGUAGE plpgsql;

--Used function in sql

Select accountbalance('A200');

in both cases receive error like this.

ERROR: column "A200" does not exist LINE 1: select EXECUTE(get_account("A200")); ^

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

ERROR: column "A200" does not exist SQL state: 42703 Character: 28

you don't execute prepared statement when you select EXECUTE(get_account("A200")); - you execute your function — Vao Tsun
– Vao Tsun, Commented Mar 22, 2017 at 13:21
I tried all options, select EXECUTE(get_account('A200')), and select EXECUTE(get_account(A200)); — M Sohail Maroof
– M Sohail Maroof, Commented Mar 22, 2017 at 13:24
Well I am able to run this EXECUTE get_account('A200'); and it gets result set, But within function it gives syntex error HERE. account := EXECUTE get_account('A200'); — M Sohail Maroof
– M Sohail Maroof, Commented Mar 22, 2017 at 13:28

Community · Accepted Answer · 2017-05-23 12:17:25Z

1

you don't execute prepared statement when you select EXECUTE(get_account("A200")); - you execute your function. Here's example of how to run prepared statements:

t=# PREPARE get_account (varchar) AS SELECT * FROM pg_tables where tablename = $1;
PREPARE
t=# execute get_account ('pg_statistic');
 schemaname |  tablename   | tableowner | tablespace | hasindexes | hasrules | hastriggers
------------+--------------+------------+------------+------------+----------+-------------
 pg_catalog | pg_statistic | postgres   |            | t          | f        | f
(1 row)

For you prepared stmt it would be

execute get_account ('a200');

Regarding using sql EXECUTE in plpgsql block (which has it's own EXECUTE - very different from sql one), please read https://stackoverflow.com/a/12708117/5315974

edited May 23, 2017 at 12:17

CommunityBot

11 silver badge

answered Mar 22, 2017 at 13:24

Vao Tsun

52.5k13 gold badges114 silver badges149 bronze badges

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

1 Comment

M Sohail Maroof Over a year ago

But this does not work within function accountbalance(), see my question, account := EXECUTE get_account('A200');

Pavel Stehule · Accepted Answer · 2017-03-22 18:50:30Z

0

Why you need it? Any embedded SQL in PLpgSQL is implicitly prepared statement. Explicitly prepared statements are not supported in PLpgSQL. Maybe you want to use dynamic SQL - see EXECUTE statement.

answered Mar 22, 2017 at 18:50

Pavel Stehule

46.7k6 gold badges104 silver badges103 bronze badges

Collectives™ on Stack Overflow

Cannot run prepared statement/function in postgres

2 Answers 2

1 Comment

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related