Tag Info

Hot answers tagged

5

You should specify the length of the varchar column e.g. varchar(2000).


3

inet_client_addr() is a system information function. It is located in the schema pg_catalog like other built-in functions (except for additional modules). pg_catalog is automatically part of the search_path. Per documentation: In addition to public and user-created schemas, each database contains a pg_catalog schema, which contains the system tables ...


2

I'm assuming your DBA has not (and will not) give permissions for SQLServerAgent. If so you will have to use another scheduler. If you want to execute a stored procedure daily at a specific time, I'd suggest Call the stored procedure from a page in your web application layer Set up a scheduler to call the web page at a daily interval. A free service ...


2

Table variables have the scope of the batch or stored procedure they are run in. There is no need to drop the table variable at the end of a batch, however you will not be able to reference the table variable in consecutive batches. If you execute the SQL as one block, without the exec, then your SELECT will be able to 'see' the table variable and select ...


2

Must be postgres user or other Superuser for mine. Only need create schema privilege for Erwin's answer. start tests: alter FUNCTION INET_CLIENT_ADDR() rename to _inet_client_addr; CREATE OR REPLACE FUNCTION inet_client_addr() returns inet language sql stable cost 1 as ' select ''192.168.0.1''::inet; '; select inet_client_addr(); end tests alter ...


1

You can have stored procedures execute as the definer instead of the invoker: CREATE DEFINER = 'admin'@'localhost' PROCEDURE p1() SQL SECURITY DEFINER BEGIN UPDATE t1 SET counter = counter + 1; END; See: Access Control for Stored Programs and Views


1

I would code as follows: SELECT (some values) FROM mydb.mytable WHERE m_dt >= CURDATE() - INTERVAL 60 DAY + INTERVAL 0 SECOND AND m_dt <= CURDATE() - INTERVAL 60 DAY + INTERVAL 86399 SECOND; DELETE FROM mydb.mytable WHERE m_dt >= CURDATE() - INTERVAL 60 DAY + INTERVAL 0 SECOND AND m_dt <= CURDATE() - INTERVAL 60 DAY + INTERVAL ...


1

You can't embed DDL in a stored procedure like this. Either use execute immediate and your DDL statement passed to it as a string, or, better, remove the stored procedure entirely an execute the DDL directly.


1

Perhaps you could add a counter to the loop so that every 100 rows, you run SLEEP for one second. I will add that code: DELIMITER $$ DROP PROCEDURE IF EXISTS `sp_UsersLoop`$$ CREATE DEFINER=`root`@`%` PROCEDURE `sp_UsersLoop`( ) BEGIN DECLARE v_BatchCount,v_BatchLimit INTEGER; DECLARE v_user_id INTEGER DEFAULT 0 ; DECLARE v_officialjobtitle ...


1

The problem you are actually facing is that you are calling two items asynchronously on your end, but they in fact need to run in order on the database end per your requirements. There is nothing that says the first call should finish before the second since it's asynchronous. The process you have in place does not make sure that happens. IMHO either use a ...


1

So it seems as though you are dealing with a shared database environment where you have access to neither 1) SQL Server Agent or 2) the hosting OS. The way I see it, your best bet would be to have a second machine that you have complete control over (i.e. this could be something as simple as a computer you have lying around) and, provided that the machine ...


1

Drop the table if it exists before the next run. ... begin drop table if exists test; create temp table test(up_link double precision, time_stamp timestamp without time zone) on commit drop; a := endIndex - startIndex; ... Or, as @dezso suggests, you could create the table only if it doesn't exist. ... begin create temp table IF NOT EXISTS test(up_link ...



Only top voted, non community-wiki answers of a minimum length are eligible