Stack Overflow
Stack Overflow

Questions tagged [firebird-psql]

The procedural language for the Firebird database. Use this tag for questions concerning Firebird stored procedures, triggers, execute block or PSQL stored functions.

Firebird calls its procedural language "psql" (Procedural SQL). This should not be confused with PostgreSQL's command line tool psql.

PSQL is used to define stored procedures, triggers, anonymous procedures (execute block), and - since Firebird 3.0 - PSQL stored functions.

Resources

See also

21 questions
3
votes
1 answer

Firebird 2.5 exception handling within autonomous transaction

I'm experiencing performance drop in one of our Firebird stored procedures and I have no clue why. I have found the following code in the mentioned SP: declare v_dummy integer; ... in autonomous transaction do begin -- insert may fail, but that is…
tcxbalage
  • 608
  • 1
  • 9
  • 24
2
votes
2 answers

How to get sum of detail table fields in master table with update trigger

There are master table 'factgad' and detail table 'recgad' in a Firebird 3.0 database. factgad: factgad_k(pr_k)... recgad: recgad_k(pr_k), factgad_k(fk)... When I update the master table, I have to get sum of detail table's records, but I couldn't…
basti
  • 167
  • 9
2
votes
1 answer

How to nest anonymous blocks with declare statements in Firebird?

In Firebird, DECLARE statements can be listed at the beginning of an EXECUTE BLOCK statement: EXECUTE BLOCK [()] [RETURNS ()] AS [] BEGIN [] END Within a block, no further DECLARE…
Lukas Eder
  • 181,694
  • 112
  • 597
  • 1,319
1
vote
0 answers

Converting SQL Server code to Firebird produces "Token unknown" error

I have my SQL Server code available, but I couldn't convert it to Firebird no matter how hard I tried. I would like your help in this matter. SQL Server and Firebird all tables are the same and the data types are the same. SQL Server Database SQL…
Burak Gürsoy
  • 11
  • 2
1
vote
1 answer

Is the cursor variable updated when updating a row?

In the code below (stored procedure body), whether the value of the cursor field is automatically updated after UPDATE or not? If not, is the Close / Open command sufficient again or not? I didn't find any description that included this, it was just…
Himer Norbert
  • 21
  • 2
1
vote
2 answers

Variable number of parameters in a Firebird stored procedure

I have the following stored procedure: ALTER PROCEDURE SP_STOCK_ANALYSIS ( MAIN_GROUP CHAR(6) ) RETURNS ( STOCK_CODE CHAR(21), STOCK_GROUP CHAR(6), DESCRIPTION CHAR(31), EXPENSE NUMERIC(15, 4) ) AS BEGIN FOR SELECT …
gdekoker
  • 161
  • 7
1
vote
1 answer

Is it possible to UNION two EXECUTE BLOCK statements?

Is there a way to perform a union of 2 sets where both were output by 2 different execute block commands? The sets structure are the same but each one contains different parameters therefore I cannot easily merge both in one execute block…
Jonathan Livingston Seagull
  • 636
  • 4
  • 14
1
vote
1 answer

I need to insert dynamically the field names and values old and new in a trigger, in firebird 2.5

I can insert table column names dynamically the problem is when I want to insert the new or old values in my log table I am getting a string 'old.Colname' or 'new.Colname' instead of old or new value. DECLARE C_USER VARCHAR(255); DECLARE VARIABLE…
Edward Chome
  • 35
  • 1
  • 5
1
vote
1 answer

How to skip/continue delete statement when the table does not exist?

I want to write execute block that I can use for multiple databases with similar structure, some don't have 1 or more tables. I use something like this execute block as begin delete from table1; delete from table2; delete from table3; delete…
Flash
  • 11
  • 2
1
vote
1 answer

TIBScript and local variables

I am working with Delphi 7 and Firebird 2.0. In my application I am using TIBScript components. The problem arises when I use local variables in the script. Firebird requires the names of local variables to be preceded by a colon in some cases.…
Rick77
  • 241
  • 3
  • 19
1
vote
1 answer

Error occurs: "Using GRANT OPTION on procedures not allowed" when creating a PSQL variable

We are using SQL manager for interbase-firebird and also Firebird 3.0.5. We are creating a procedure and that works fine. However from the moment we add a psql variable to the procedure we get below error. When we remove the variable the procedure…
free
  • 135
  • 2
  • 17
1
vote
1 answer

Firebird 2.5 commit/rollback (using autonomous transaction) in loop

I have to process records in a stored procedure's loop, for example: create or alter procedure process_waiting_records as declare v_id type of column my_table.id; begin for select t.id from my_table t …
tcxbalage
  • 608
  • 1
  • 9
  • 24
1
vote
1 answer

"Column does not belong to referenced table" Why?

This code should delete an album but I don't know why it still doesn't work declare variable al_eliminare integer; begin select count(album.id) from album where id = :id_album into :al_eliminare; if(al_eliminare = 0) then delete…
Octavian Fusari
  • 19
  • 2
1
vote
1 answer

Resolving a singleton error in a stored procedure

I have the following stored procedure in Firebird SQL: ALTER PROCEDURE SP_REPORT_USD ( PER SMALLINT ) RETURNS ( ACCOUNT_NUMBER CHAR(21), AMOUNT NUMERIC(15, 4) ) AS BEGIN SELECT L.ACCOUNT_NUMBER,…
gdekoker
  • 161
  • 7
1
vote
1 answer

Assistance with Firebird SQL stored procedure

I have a very simple stored procedure: ALTER PROCEDURE SP_BALANCE_USD ( PER SMALLINT ) RETURNS ( ACCOUNT_NUMBER CHAR(21), AMOUNT NUMERIC(15, 4) ) AS BEGIN SELECT L.ACCOUNT_NUMBER, SUM(-(CURRROUND(L.DEBIT,2) -…
gdekoker
  • 161
  • 7
1
2