I want to update a scalar function in our database. This function is used for several calculated columns in a key table so I get a dependency error if I try to update it. I can explicitly remove the columns, update the function and re-add the columns but, without a lot of extra fannying about, that will change column orders and might have other unintended consequences. I wondered if there was a cleaner way of doing it?

I am new to advanced sql programming. I am storing constants. Constants have three possible scopes: local, user and global. Two tables: s0constants_user holds set values of constants s0constants holds the constant definitions and values. CREATE TABLE public.s0constants_user ( constant_name bpchar(15) NOT NULL, empfkey int2 NOT NULL DEFAULT 0, constant_value bpchar(255) NULL, locality_name bpchar(30) NOT NULL DEFAULT 1, CONSTRAINT s0constants_user_pk PRIMARY KEY (constant_name, empfkey, locality_name) ); CREATE TABLE public.s0constants ( constant_name bpchar(15) NOT NULL, constant_value bpchar(255) NULL, constant_data_type bpchar(1) NULL, cttype int4 NULL, displayname bpchar(30) NULL, actiontype int4 NULL, note text NULL, const_scopefk int4 NOT NULL DEFAULT 3, -- 1=local 2- user 3=global CONSTRAINT "S0CONSTANTS_pkey" PRIMARY KEY (constant_name), CONSTRAINT scope_fk FOREIGN KEY (const_scopefk) REFERENCES s0constants_scopelk(id) I realize I could do this with separate tables for each scope but I'd like to make this work even if it is just a learning exercise. I need a function to return and constant's value and data type given the constant name and empfkey. There are different queries for each scope of constant. local - constant_name, empfkey, locality_name user - constant_name, empfkey global - constant_name Constant names are unique over the three scopes. I thought a case statement might do it but I'm having trouble limiting it to one row of data and how to set up the sub-queries. This is my first(Edit SECOND) pass at this but now I have issues with "subquery must return only one column" FUNCTION public.const_get( stname text, empid INT, locality text ) returns TABLE(cvalue text, ctype text) LANGUAGE sql STABLE PARALLEL SAFE AS $$ select c.const_scopefk, c.constant_data_type ,case when const_scopefk = 1 then --local ( select distinct scu.constant_value, c.constant_data_type from s0constants_user scu where (scu.constant_name = constant_name and scu.empfkey = empid and scu.locality_name = locality) limit 1) when const_scopefk = 2 then --user (select scu.constant_value, c.constant_data_type from s0constants_user scu where (scu.constant_name = constant_name and scu.empfkey = empid and scu.locality_name = 1) limit 1) when const_scopefk = 1 then --global (select scu.constant_value, c.constant_data_type from s0constants_user scu where (scu.constant_name = constant_name and scu.empfkey = 0 and scu.locality_name = 1) limit 1 ) end from public.s0constants c where c.constant_name = UPPER(constname); $$ EDIT: I've updated table definitions

I have defined a function that returns a table. In this function, I use multiple temporary table to pre-elaborate data. Here, there is a very simple example: create or replace function public.fn_world_cities() returns table ( city_id int, city_name varchar, country_id int ) as $$ begin ---- Extract temp table with all cities create temporary table tmp_all_cities on commit drop as begin $$ select city_id, city_name, country_id from public.table_world_cities where row_status = 'A'; ---- filter active record ---- Return data return query select city_id, city_name, country_id from tmp_all_cities; end; $$ language plpgsql; The temporary table creation is time expensive. When I use the function, always use some WHERE condition. Like: select * from public.fn_world_cities() where country_id = 10; In my function, there are many fields on which I can set a WHERE, so it's impossible to add function parameters for everyone. Is there a way, at run time, to know there WHERE condition values? In that way I can pre-filter data during temporary table creation. I wish I can have a function like this: create or replace function public.fn_world_cities() returns table ( city_id int, city_name varchar, country_id int ) as $$ begin ---- Extract temp table with all cities create temporary table tmp_all_cities on commit drop as begin $$ select city_id, city_name, country_id from public.table_world_cities where row_status = 'A' ---- filter active record and ***WHERE CONDITION APPLIED TO FUNCTION CALL*** ; ---- Return data return query select city_id, city_name, country_id from tmp_all_cities; end; $$ language plpgsql;

I followed https://dba.stackexchange.com/questions/81369/select-all-substrings-n-grams-of-length-n to get a function for generating all n-grams for text types. Works great. I figured out how to cast my bit-strings to text, and the function from that link works. However I need it to be as fast as possible, thus I wanted a bit-string native implementation, and I came up with this:

CREATE OR REPLACE FUNCTION public.ngrams(in input bit varying, in len integer)
 RETURNS SETOF bit
 LANGUAGE sql
AS $function$
SELECT cast((input << alpha) as bit(4))
FROM GENERATE_SERIES(0, LENGTH($1)-($2+1), 1) alpha;
$function$

This works, for N=4. However when I tried to change it to $2 or len I would get:

ERROR:  22P02: invalid input syntax for type integer: "len"
LINE 5: SELECT cast((input << alpha) as bit(len))
                                        ^

How come I can use the named parameter from the function declaration for input but not for len? Or better yet, how can I feed a variable into that spot?

I have 31 tables one for each day of the month where the table names are like subscription_x where x is the day of the month On a daily it should run a select * from subscription_x to get data from the table of the previous day e.g if today is Dec 14th it should run select * from subscription_13 then tomorrow Dec 15th it will run select * from subscription_14 Can someone help please need a way to do this in sql

After installing via alternatives the latest IBM JDK on redhat 8.10 (Ootpa) x86 and when trying to update the Java Version for Db2 with db2 UPDATE DBM CFG USING jdk_path SDK for Java-path to

(...) (JDK_PATH) = /dbhome//sqllib/java/jdk64

following error appears:

MESSAGE : ADM0508E  The database manager was unable to load the Java
          interpreter library "/usr/lib/jvm/jre/lib/amd64/j9vm".  This error
          commonly occurs because of restrictions imposed by the operating
          system.  Consult the IBM documentation for a solution.  If this
          problem persists, contact IBM support.

**Db2 version:** DB2 v11.5.9.0, special_52441 "DYN2501101324AMD64_52441" **Java version** "21.0.6" 2025-01-21 LTS IBM Semeru Runtime Certified Edition 21.0.6.0 (build 21.0.6+7-LTS) Eclipse OpenJ9 VM 21.0.6.0 (build openj9-0.49.0, JRE 21 Linux amd64-64-Bit Compressed References 20250121_373 (JIT enabled, AOT enabled) **Question 1:** Where is the path in the error message configured, as it turns out to be not the one being defined as JDK_PATH. **Question 2:** Is it actually possible to update the referred Db2 version to the latest JDK/JRE release and has it to be specifically an IBM JDK/J9vm? **Question 3:** Is there any good article or documentation how to update/handle/work with Java UDFs in Db2? *Correlates to: https://stackoverflow.com/questions/69019194/how-to-upgrade-java-to-at-least-8-0-6-25-in-ibm-db2-v11-1*

Here's a table: CREATE TABLE t_heights (id INT, height REAL); INSERT INTO t_heights VALUES (1,53.63), (2,45.19), (3,47.06); Reading through the table rows with a FOR...LOOP and a RECORD type variable like this works: CREATE OR REPLACE FUNCTION fnct_row_by_row (input_table regclass) RETURNS VOID LANGUAGE plpgsql AS $$ DECLARE current_row RECORD; BEGIN FOR current_row IN EXECUTE 'SELECT * FROM '||input_table LOOP RAISE NOTICE 'field value: %',current_row.height; -- *<<< hardcoded field name* END LOOP; END; $$ ; Results: SELECT fnct_row_by_row ('t_heights') ; NOTICE: field value: 53.63 NOTICE: field value: 45.19 NOTICE: field value: 47.06 However, the field extracted from the RECORD variable needs to have its name hardcoded. **How to specify dynamically the field to be extracted?** The following generates an error: CREATE OR REPLACE FUNCTION fnct_row_by_row2 (input_table regclass, input_field_name TEXT) RETURNS VOID LANGUAGE plpgsql AS $$ DECLARE current_row RECORD; BEGIN FOR current_row IN EXECUTE 'SELECT * FROM '||input_table LOOP RAISE NOTICE 'specified field: %',current_row.input_field_name; -- *<<< field name from parameter* END LOOP; END; $$ ; Execution: SELECT fnct_row_by_row2 ('t_heights','height') ; ERROR: record "current_row" has no field "input_field_name" CONTEXTE : SQL statement "SELECT current_row.input_field_name" PL/pgSQL function fnct_row_by_row2(regclass,text) line 10 at RAISE

Why does MariaDb use these terms differently?

#drop table dbstructA;
    create table dbstructA(datum date);
    insert into dbstructA values('2025-01-01');
    insert into dbstructA values('2025-01-02');
    insert into dbstructA values('2025-01-03');
    insert into dbstructA values('2025-01-04');
    insert into dbstructA values('2025-01-05');
    insert into dbstructA values('2025-01-06');
    insert into dbstructA values('2025-01-07');

select date_format(datum,'%a') as day,
dayofweek(datum) as daynum,
weekday(datum) as numday 
from dbstructA where datum>='2025-01-01';

I have a PostgreSQL for my accounting system and a MySQL database for my php website, i want to sync these two database for every update may occurred on PostgreSQL it also updates MySQL database, they are on linux operating system(ubuntu), How can i do this work?

I wrote this function:

create or replace function field_summaries(gap interval , _tbl anyelement, _col text)                                
returns table(hour text, average numeric, minimum numeric, maximum numeric) as
$func$   
begin
 RETURN QUERY 
  EXECUTE format('select time_bucket($1, time)::text as hour,                                                                                      
                         avg(%I), 
                         min(%I),
                         max(%I)                                                                                                         
                  from %I d                                                                                                         
                  where d in (select device_id from sensors)                                                                                           
                  group by hour', _col, _col, _col, _tbl)
     USING gap; --<< this passes the parameter to the EXECUTE command
END;
$func$ 
language plpgsql
stable;

to which I pass a table name and a column name.I call it like this : select field_summaries('5minutes', NULL::m_09, 'current'); which works fine. I need to call this function from my node.js code. so I need to pass a string as an argument. When I pass "Null::m_09" as a string I get an error. Is there a way to alter the function so that I can call it like this : select field_summaries('5minutes', m_09, 'current'); Right now I get this when I do it:

field_summaries('5minutes', m_09, 'current');
ERROR:  column "m_09" does not exist
LINE 1: select field_summaries('5minutes', m_09, 'current');
                                           ^

Any help is much appreciated.

I'm facing a bit of a problem on some restrictions I'm trying to implement on my postgresql database. My dilemma is as follows: I have an event table that logs basically everything that happens on my app, it can't be updated or deleted directly from the app (only inserts work), but I also need to prevent anyone from updating or deleting using a manual query (when conecting from dbeaver for example), I know I can do this by revoking permissions to the table, problem is, at the same time, I need to insert into that table an event that someone tried to manually update it, and when I revoke restrictions its becoming impossible. I was creating a trigger as follows, and the restriction works but its not inserting anything into my table, could anyone help me out? --Create event on event table update attempt CREATE OR REPLACE FUNCTION protect_events_on_update() RETURNS TRIGGER AS $BODY$ DECLARE username VARCHAR; BEGIN -- Get special variable values SELECT current_user INTO username; INSERT INTO events (uuid,description) VALUES (someUUID, username || 'tried to modify the table'); RETURN NEW; END; $BODY$ language plpgsql; CREATE TRIGGER protect_events_on_update_trigg BEFORE UPDATE ON events FOR EACH row EXECUTE PROCEDURE protect_events_on_update(); REVOKE ALL PRIVILEGES ON TABLE events FROM user; GRANT INSERT ON TABLE events TO user; GRANT SELECT ON TABLE events TO user;

Many times, I'm able to write a PL/PGSQL UDF in two ways: 1. As a big query usually using WITH clauses and UNION (i.e. more declarative but also more complex to write/read) 2. As multiple smaller queries (i.e. more procedural but also easier to write/read) I'm aware that PG query planner will plan each smaller query in UDF individually, and I'm afraid that might have an impact on overall performance. What do you guys prefer? Thank you.

I'm trying to add a notice (or something similar) to a function that is executed on update by a trigger in PostgreSQL 9.6 db. I want to let the user who edited the table that something happened. I don't know is "NOTICE" is the proper RAISE (Why not "INFO"?). The central question is: **how I can add the raise notice argument after the update?**

I need to be able to parse a string array with STRING_SPLIT on a SQL 2016 instance. The vendor has required the one user DB we have access to be set with a compatibility level of 100 while the system DBs are at 130. Can STRING_SPLIT and other system functions be referenced when the context is set to the 100-level DB?

I need to iterate over a result set and update records from the data. I am using an anonymous block but the information does not persist. Like this... do $$ declare dados Record; begin for dados in select vd."Id" from "Vendas" vd inner join "ItensVendas" iv on iv."VendaId" = vd."Id" where vd."Especie" = 'NFE' and coalesce(vd."ClienteId", 0) = 0 and iv."CodCFOP" in (5949, 5927) group by vd."ChaveNFE", vd."Id", vd."DataCadastro" loop execute format('update "Vendas" set "EhConsumoProprio" = true where "Id" = %L', dados."Id"); end loop; end $$; And again... do $$ declare dados Record; declare cmd varchar(300); begin for dados in select vd."ChaveNFE", vd."Id", vd."DataCadastro" from "Vendas" vd inner join "ItensVendas" iv on iv."VendaId" = vd."Id" where vd."Especie" = 'NFE' and coalesce(vd."ClienteId",0)= 0 and iv."CodCFOP" in (5949, 5927) group by vd."ChaveNFE", vd."Id", vd."DataCadastro" loop raise notice '%',dados."Id"; update "Vendas" set "EhConsumoProprio"=true where "Id"=dados."Id"; -- It don't persists cmd := format('update "Vendas" set "EhConsumoProprio"=true where "Id"=%L', dados."Id"); raise notice '%', cmd; execute cmd; -- It don't persists end loop; end $$;

I have a procedure:

create or replace procedure pro_update_last_read(in input_sensor_id integer, in read_time timestamp)
as
$$
begin
    update sensor
    set last_read = read_time
    where sensor_id = input_sensor_id;
end;
$$ language plpgsql;

And a trigger that calls it:

create trigger tri_last_read
    after insert or update of report_time
    on report
execute procedure pro_update_last_read(sensor_id, report_time);

However, when creating the trigger, I get the error message: > ERROR: function pro_update_last_read() does not exist Why is this error happening?

I have the following composite data type in a postgres db: CREATE TYPE IncreaseCredentialsType AS ( increase_account_id text, api_key text, url text ); I'm returning a value of this type at the end of a function, like so. The exception I'm throwing just to see what the result looks like before the function returns. result := (increase_account_id, api_key, url)::IncreaseCredentialsType; RAISE EXCEPTION 'get_increase_credentials about to return %', result; RETURN result; When I do that, it prints: ERROR: get_increase_credentials about to return (,,https://increase.com/api/v1) (By the way, the first two parameters are blank on purpose). I'm then commenting out that exception and letting the control flow back to the calling function to see what I get in that context: SELECT public.get_increase_credentials(account_id) INTO result; RAISE EXCEPTION 'get_increase_credentials returned %', result; When I do that, I get: ERROR: get_increase_credentials returned ("(,,https://increase.com/api/v1) ",,) The first field (

.increase_account_id

) has the entire result as a string, with the parentheses and commas. What am I doing wrong that is making this handoff not work? In both cases, the variable I'm assigning to is of type

.

I have a DB with 180 tables, and I was doing a logical deletion, now I need to do some testing with physical deletion. Is there a way to alter all my tables and to drop their fk's and add then add their corresponding fk's with its on delete cascade?

If you were going to write 10 procedures that all have the same exceptions, you'd want to just reference the procedures defined somewhere else, right? You can call a function inside a procedure

create or replace procedure P
    a_variable_name number;
begin
    a_variable := a_function('a parameter');
end;
/

And i imagine you'd have to pass the result of your procedure into the function. Like

if X procedure executed correctly, then do nothing
if Y error, do the Y exception handling

Is such a thing possible? If not, how do oracle users handle writing 10 different procedures that all use the same exception handling? Like, are there any tips and tricks besides just copying and pasting those exceptions and hoping you'll never have to change all 10 of them if you have to change one thing?

I am working with SQL Server 2008 and I am looking for a function like ltrim and rtrim which will also remove leading and trailing tabs, double spaces, carriage returns, line feeds, etc. There are a number of functions out there but the ones I found all have limitations, e.g. truncate the string to 8000 characters. e.g. (according to some of the comments): SQL SERVER – 2008 – Enhenced TRIM() Function – Remove Trailing Spaces, Leading Spaces, White Space, Tabs, Carriage Returns, Line Feeds One of the comments proposed a better solution but the - 1 causes an incorrect syntax error and I am not sure why. CREATE FUNCTION dbo.SuperTrimLeft(@str varchar(MAX)) RETURNS varchar(MAX) AS BEGIN IF (ASCII(LEFT(@str, 1)) < 33) BEGIN SET @str = STUFF(@str, 1, PATINDEX('%[^'+CHAR(0)+'-'+CHAR(32)+']%', @str) – 1, ' '); END; RETURN @str; END; So my question is, what is the best approach to accomplish the task above?