Tag Info

New answers tagged

0

A recursive CTE seems the way to go. Assuming your path has no cycles. Else it needs more work to detect cycles. The array solution below can readily be adapted. Test setup Building on this simplified layout: CREATE TABLE t1 (t1_id int, objid text); INSERT INTO t1 VALUES (1,'aaa') ,(2,'bbb') ,(3,'ccc') ,(4,'ddd') ,(5,'eee') ,(6,'fff') ,(7,'ggg') ...


1

To answer your question; WHY? You probably already know this by now since the post is 2 years old. But I'll respond just for the record. The reason #1 requires a commit and #2 doesn't is because the default database setting in Oracle is to commit a transaction when a session ends. If you are in sqlplus and run your code manually, it will not commit the ...


1

Looking at the original query SELECT COUNT(*) as raw_views ... FROM logs WHERE timestamp >= CURDATE() GROUP BY DATE(timestamp) It appears that you are looking for counts for just today. In that instance, why use GROUP BY at all ? You should run SELECT COUNT(*) as raw_views ... FROM logs WHERE timestamp >= CURDATE(); OK, let's get a little more ...


1

First create a Calendar table (a table of dates) as described in this answer; then extend it by adding and populating Year, MonthName, DayOfMonth, and Tomorrow columns in that table and add a unique index on it by (Year, MonthName and DayOfMonth), and another unique index on it by the base column (Date) - the Primary Key. Now you can generate your reports ...


0

This might work: select sum(N), the_date from ( SELECT COUNT(*) as N, timestamp, DATE(timestamp) as the_date FROM logs WHERE timestamp >= CURDATE() group by timestamp ) as A GROUP BY the_date -- or: GROUP BY DATE(timestamp) Your first problem is MySQL, for many reasons, here because function(column) shouldn't preclude the use of an ...


0

You could create an additional column, date, which stores the function date(timestamp) on insertion. This won't make the group by extremely efficient, but it can avoid the temporary table. The second problem is the range + GROUP BY, which would make an index on (timestamp, date) useless (BTREE limitations). You can create just an index on (date) or better, ...


2

If you have a function that does an UPDATE and you try to call that from a SELECT statement, you should get an Oracle error saying that you cannot do DML inside a query. So your SELECT statement should be throwing an error. My first guess would be that you have an exception handler somewhere that, intentionally or not, is catching and discarding the error. ...


1

Do the exclusion in the cron job definition. The handy flock shell command is intended for exactly this. To do it inside PostgreSQL you would have the function take a lock that it holds for the duration of its run. Lots of options - LOCK TABLE ... IN EXCLUSIVE MODE, do a SELECT ... FOR UPDATE of a particular row, or use pg_advisory_xact_lock. See the ...


6

I suggest an SQL function: CREATE OR REPLACE FUNCTION foo(_date date) RETURNS TABLE ( name text -- types have to match your actual types! , keyword_id int , project_id int , the_date date , today int , yesterday int , week int , month int) AS $func$ SELECT k.name, f.keyword_id, f.project_id, _date -- AS the_date -- col ...


2

Question asked There is a built-in way to log all statements inside plpgsql functions: auto-explain LOAD 'auto_explain'; SET auto_explain.log_min_duration = 1; -- exclude very fast trivial queries SET auto_explain.log_nested_statements = ON; -- statements inside functions Details under this closely related question: Postgres query plan of a UDF ...


0

Great dezso, it works! Here is the final version of my function: CREATE OR REPLACE FUNCTION fnct_clear_temp_fields() RETURNS VOID AS $$ DECLARE dataset_1_row RECORD; --Record variable to go through each row of the view below update_query TEXT; --The dynamic UPDATE query to be executed BEGIN FOR dataset_1_row IN --Cycle through rows of query ...


1

So, my suggestion as an actual answer: If you need it only in this function, you can do a RAISE LOG '%', your_statement;, or in your actual code: ... DECLARE exec_str text; ... --Set to NULL the contents of the current 'temp_' column exec_str := 'UPDATE '||dataset_1_row.table_name|| 'SET '||dataset_1_row.column_name||'=NULL ...


4

You cannot use a WITH (NOLOCK) on a Table-Valued Function, unless you put it on every single table in the code inside the function. Your best bet would be, like you said, to SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED. To change this back to the default, you need to find out what isolation level is currently set (before changing it above). This can ...


0

I can't answer to question 1 as I do not have enough information about the MySQL instances offered by different hosting providers. To answer the rest of your questions, yes, if you can leverage the features of the RDBMS you're using, you might replace PHP code with SQL scripts that can run faster than your code. Just think about the time you're saving up by ...


0

I think your problem is simply having a BEFORE trigger - it fires before the row is inserted which cannot appear in the view yet. Change it to AFTER and (after considering Craig's suggestion) you are done.



Top 50 recent answers are included