So I am fairly new to sql and there is a particular query I make where the DB always timeout. I tried everything, including restarting the server. There is this table where it listed all the events happening in an app, together with the person_role and the platform version it happened. So an example columnns and rows would be: platform_id | aggregate_id | job_id | creator_id | event_type | timestamp | client | role | app_version| so for example if I want to just check all the events associated with a particular job_id Ill just use a WHERE statement. Also take note that when I query other tables, there is no timeout, just this particular table. I tried asking around and somebody recommended a stored procedure function to make the query faster. I tried it and actually it is just an empty table. Any other suggestions? This is the simple query : SELECT * FROM db.events WHERE job_id = '1234566677' To know all the events associated with that particular job_id.

I am using SQL server 2017, I want to write SQL stored procedure to generate a fixed length/width file without using SSIS or wizard TABLE: |EmpID|EmployeeName|Department|Birthdate|Hiredate| |-----|------------|----------|---------|--------| |001|John Doe|Marketing|1986-08-31|2006-04-01| |002|Jane Doe|Sales|1975-01-17|2000-06-27| |003|Mark Vaughn|Production|1993-05-09|2017-03-06| |004|xxxxx xxxxx|xxxxxxxxx|yyyy-mm-dd|yyyy-mm-dd| |004|xxxxx xxxxx|xxxxxxxxx|yyyy-mm-dd|yyyy-mm-dd| TO

EmpID       EmployeeName        Department     Birthdate     Hiredate
001         John Doe            Marketing      1986-08-31    2006-04-01   
002         Jane Doe            Sales          1975-01-17    2000-06-27
003         Mark Vaughn         Production     1993-05-09    2017-03-06
004         xxxxx  xxxxx        xxxxxxxxx      yyyy-mm-dd    yyyy-mm-dd
004         xxxxx  xxxxx        xxxxxxxxx      yyyy-mm-dd    yyyy-mm-dd

Where 1.EmpID (10) 2.EmployeeName (40) 3.Department(20) 4.Birthdate(8) 5.Hiredate(8)

As I understand, in Postgres, procedures are for manipulating data and functions are for reading data. I would like to: 1. declare a variable 2. select a value into a variable 3. insert the variable into another table 4. do this in a transaction 5. do this in a stored procedure 6. roll back if necessary

CREATE PROCEDURE test_variable() LANGUAGE SQL AS $$ BEGIN; DECLARE some_id INTEGER; SELECT nextval('some_sequence') INTO some_id; INSERT INTO some_table (some_column) VALUES (some_id); END; $$; The above is not working out for me. When I search for solutions, there's so many different variables involving functions, $$, declaration, thing not returning anything; can't seem to find a simple example; I just need a clear example of the syntax.

I need to set the datestyle to "dd/mm/yyyy" before calling a procedure and getting it's return value back to bash. I tried the following:

read return <<< $(psql \
                  -x \
                  -U postgres \
                  -d MY_DB \
                  --quiet`\
                  -t \
                  -c "SELECT CURRENT_TIMESTAMP")    //just to make sure the date format is correct 
echo ${return} | cut -d'|' -f 2

I'm getting back a normal date format yyyy-mm-dd.

Is there any possibility to auto run procedure after a specific time/date? For example I want to auto run this stored procedure after 2 days: CREATE OR REPLACE PROCEDURE deleteresetlinks AS BEGIN DELETE FROM passwordresetlink WHERE date_created <= TRUNC(SYSDATE) - 3; END;

Is it possible to inject the name of a function (AVG, MAX...) as a procedure parameter? CREATE PROCEDURE test(func TEXT) LANGUAGE PLPGSQL AS $$ BEGIN DROP TABLE IF EXISTS foo; CREATE TEMPORARY TABLE foo AS SELECT func(bar.column_a) as func_column_a FROM bar; END $$;

I am a beginner to SQL and currently practicing the concept of the stored procedure. I have a table Fetch_Marks that contains 20 records with Student_No and Marks as columns. Now I would like to create a stored procedure for this in which the input parameter will be Student_No and when we enter the input (Let's say I gave the Student_No as 1), it should fetch the row of that particular student from the table Fetch_Marks. Is there any workaround for this? If yes, please help.

My stored procedure OUT parameter, always return a null value. Here is sample Table, Trigger and Procedure code. Table: test
Columns: - id - Int - status - enum(‘pass’, ‘fail’) - status - enum(‘pass’, ‘fail’) (null is allowed) Values in a table: id | status 1 | null Trigger: create trigger BEFORE_UPDATE_TEST before update on test for each row begin call Test_BEFORE_UPDATE_TEST(old.id, @updatedStatus); ## I always get @updatedStatus null/nil if (@updatedStatus is not null and @updatedStatus new.status) then set new.status = @updatedStatus; end if; end; Procedure: create procedure Test_BEFORE_UPDATE_TEST ( IN id int(5), OUT status enum(‘pass’, ‘fail’) ) begin @status = ‘pass’; END; What is wrong with this code, as I get unexpected result as null in the value @updatedStatus, which should be 'pass'. I looked around following QAs on dba.stackexchange but could't find solution. I use MySQLWorkbench in MacOS Catalina and version of MySQL is 8.0.19.

Suppose I have a table with CREATE, CREATE TABLE TEST ( year int, month int, date int, hr int, min int, sec int, timestamp timestamp, value double ); CREATE FUNCTION timestamp_insert() RETURNS trigger LANGUAGE plpgsql AS $$ DECLARE datestr TEXT ; DECLARE timestr TEXT ; begin datestr := new.year || '-' || new.month || '-' || new.date || ' ' || new.hour || ':' || new.min || ':' || new.sec;+ new.timestamp := datestr :: TIMESTAMP return new; end; $$ CREATE TRIGGER timestamp_insert BEFORE INSERT OR UPDATE ON TEST FOR EACH ROW EXECUTE PROCEDURE timestamp_insert(); Now I want to update a certain row based on the timestamp currently on the table and that from a new set of data. Something like, UPDATE TEST SET value = ? WHERE timestamp < "generated timestamp from a new set of (year, month, date, ...)" Is it possible to do something like this in SQL or do I need to programmatically genrate the timestamp and then simply pass that value in SQL?

People are occasionally moved between different departments. I want to create a procedure that accepts a name (name of person) and a department(department that person is assigned to) and changes the person's department assignment to a new department. for example: create procedure assign ( in name VARCHAR(256), in department VARCHAR(256) ) comment "move work to new department" BEGIN UPDATE name SET department = newdepartment END$$ please ask if you need more information for better understanding.

SQL Newbie here. I have a question about pulling multiple fields from a database when one value is not in another table. I have two tables: - "Contact" table - contains all of our contact information such as their email address, contact creation date, department, job, etc. This is considered our master table. - "Event" table - contains information regarding a contact and their email activity such as their email address, email delivery status, email create date, etc. The db uses the email address as the joining key. I don't have the privileges (due to work restrictions) to add a new PK. The task is to find all contacts in the Contact table that are not in the Event table, and pull their email address AND their contact creation date. The Event table does not have the contact creation date. When I select only the email address it works fine, but when I try to add in the second field (create date), it gives me an error. The query I'm using is below. Any help is appreciated! SELECT e.recipient, c.contactProperty_createdate FROM Event as e WHERE NOT EXISTS (SELECT c.emailAddress, c.contactProperty_createdate FROM Contact as c WHERE c.emailAddress = e.recipient);

Using PostgreSQL 11 Beta 2, a very simple trading_holiday table is created in the config schema: DROP TABLE IF EXISTS config.trading_holiday; CREATE TABLE config.trading_holiday ( id smallint GENERATED ALWAYS AS IDENTITY NOT NULL, holiday_date DATE, name CHARACTER VARYING(80) ); ALTER TABLE config.trading_holiday ADD CONSTRAINT trading_holiday_pk PRIMARY KEY (id); Then a very simple stored procedure, config.sp_add_holiday is created to add holidays to the table: CREATE OR REPLACE PROCEDURE config.sp_add_holiday(holiday_date DATE, name CHARACTER VARYING(80)) AS $$ BEGIN INSERT INTO config.trading_holiday(holiday_date,name) VALUES(sp_add_holiday.holiday_date,sp_add_holiday.name); END $$ LANGUAGE PLPGSQL; Then a simple call is made to add the first holiday: CALL config.sp_add_holiday(holiday_date='2018-01-01',name='New Years Day'); And I get the following error message: [2018-08-07 11:56:18] ERROR: column "holiday_date" does not exist [2018-08-07 11:56:18] Position: 21 Doing a manual insert, e.g.: INSERT INTO config.trading_holiday(holiday_date,name) VALUES('2018-01-01','New Years Day'); Works successfully: [2018-08-07 12:04:01] 1 row affected in 2 ms Despite being new to the PostgeSQL 11 SQL procedure functionality (who isn't?), this seems like such a simple proc. What in the world am I doing wrong?